Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dmilam
Posting Yak Master
185 Posts |
 Posted - 2010-12-15 : 12:03:52
|
Don't want to hijack the other thread, but I am wondering why my simpler query takes nearly 4 minutes to execute.SET NOCOUNT ON;SELECT COUNT(M1.ID)FROM Table1 AS M1WHERE M1.Code = 'XX09'AND NOT EXISTS (SELECT *FROM Table1 AS M2WHERE M2.Code = 'XX10'AND M1.ID = M2.ID) I'm aiming to return all IDs from activity XX09 who did not also get recorded from activity XX10. Unfortunately I don't have SHOWPLAN permission to examine the actual execution plan. |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-12-15 : 12:38:21
|
| Is ID indexed?Is Code indexed?And how selective are they?You really need showplan permission to troubleshoot performance issues.Rule of thumb: index fields used in WHERE clauses and JOIN conditions -- thus why I ask about those two. |
 |
|
|
dmilam
Posting Yak Master
185 Posts |
Posted - 2010-12-15 : 12:51:39
|
quote:
Originally posted by russell
Is ID indexed?Is Code indexed?And how selective are they?
According to SELECT object_name(object_ID),STATS_DATE (object_id, index_id) AS last_update,*FROM sys.indexesorder by1 the table M1 has none.Not sure what you mean by 'selective' - ? |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-12-15 : 13:05:42
|
| If there is no index then you're scanning the table twice to accomplish this.Selectivity refers to how dispersed the values in a column are. Many different values, or more specifically, many different values per # of total rows, can be considered highly selective. Few different values is not highly selective.A column that is not highly selective is not likely to benefit from an index, as the optimizer will just choose to do a table scan anyway. |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2010-12-15 : 13:11:34
|
quote:
Originally posted by dmilam
Don't want to hijack the other thread, but I am wondering why my simpler query takes nearly 4 minutes to execute.<snip>
Simpler than what? Can you post both queries you are interested in comparing? |
|
|
|
dmilam
Posting Yak Master
185 Posts |
Posted - 2010-12-15 : 13:41:38
|
quote:
Originally posted by russell
If there is no index then you're scanning the table twice to accomplish this.Selectivity refers to how dispersed the values in a column are. Many different values, or more specifically, many different values per # of total rows, can be considered highly selective. Few different values is not highly selective.A column that is not highly selective is not likely to benefit from an index, as the optimizer will just choose to do a table scan anyway.
Thanks. I'll see if moving the query into a T table and then adding an index helps at all. In this case I don't think the values are that selective.quote:
Originally posted by LampreySimpler
Simpler than the query in the other thread, I meant. |
|
|
|
|
|
|
|
|
Another query speed question