| Author |
Topic |
|
fiffreak150
Starting Member
4 Posts |
 Posted - 2010-09-29 : 11:43:29
|
| I have a query I am trying to create an index for to improve performance. The query essentially performs a SELECT *, GETDATE() only all the columns are typed out. The Where clause is based on a CHAR(1) column that is being used essentially as a flag. Select *, GETDATE() from logtable WHERE aflg = 'a'I created a non-clustered index on the aflg column but the query plan is performing a table scan which is killing performance. Since this is a log table there is only one other index, and that is on a date column, also a non-clustered index. I added a table hint to the query and it ran much faster but I would prefer not to have to do this. any suggestions? |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-09-29 : 12:24:10
|
| What % of rows does that return? The index is not covering, so unless it's a very low % (<1%) SQL's unlikely to use the index as it costs the key lookups as far too expensive.--Gail ShawSQL Server MVP |
 |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2010-09-29 : 12:24:43
|
| my guess:the table doesn't have a clustered index so when the index for aflg is read there isn't anything for it to match on for the rest of your columns. (if there was a clustered INDEX I think you'd probably get an index seek followed by a Clustered Index lookup).Edit: As GilaMonster mentions this is an expensive thing to do (lookups) as it has to generate a lookup list which you would see as an RID lookup. You only get these on HEAP tables (tables without a CI)Add a clustered index. as this is a log table I'd suggest adding an autonumber column as the CI (and PK why not). Insert performance will actually improve with a constantly increasing primary key / CI.AND / ORInclude the columns returned from the SELECT statement in the index you have either as covering index or including the data as leaf nodes with the INCLUDE option.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
fiffreak150
Starting Member
4 Posts |
Posted - 2010-09-29 : 13:09:26
|
| currently it would return about 600K of 71 million (0.008%)This is part of an archiving/purging process so hopefully that number would shrink down to an average of 600-700k out of 8 million.I'll try the clustered index and see if that solves my problem. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-09-29 : 13:12:43
|
quote:
Originally posted by Transact Charlie
Edit: As GilaMonster mentions this is an expensive thing to do (lookups) as it has to generate a lookup list which you would see as an RID lookup. You only get these on HEAP tables (tables without a CI)
However the key lookups you get on a table with a cluster are virtually the same things and just as expensive.--Gail ShawSQL Server MVP |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-09-29 : 13:13:28
|
| With that kind of selectivity it should be using the index. Any chance you can upload the execution plan somewhere? (saved as a .sqlplan file and zipped)--Gail ShawSQL Server MVP |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2010-09-30 : 04:26:47
|
quote:
Originally posted by GilaMonster
quote:
Originally posted by Transact Charlie
Edit: As GilaMonster mentions this is an expensive thing to do (lookups) as it has to generate a lookup list which you would see as an RID lookup. You only get these on HEAP tables (tables without a CI)
However the key lookups you get on a table with a cluster are virtually the same things and just as expensive.--Gail ShawSQL Server MVP
OK. cheers, thanks for the correction. I would have thought there would have been some extra overhead involved indexing each side of the join on a heap.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
fiffreak150
Starting Member
4 Posts |
Posted - 2010-09-30 : 11:08:03
|
Alright I have tow sqlplans for you, one is the execution plan for the normal query, the second is the execution plan if I use the table hint so it uses the index.http://www.mediafire.com/?u3vng73zfkb8n4iYou guys are right that the SQL Server thinks using the index and performing an RID lookup will be more expensive then the table scan,  but I have ran both queries and after it had run for more the 4x the length of the query with the table hint I got tired of waiting. no info on if the clustered index helps, due to an unrelated issue the creation of the index failed. thanks |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-09-30 : 12:06:12
|
| tried updating the statistics? if u are frequently adding records, it is likely that stats are out of date.a char(1) is not a very good candidate for an index either. if the value you're using has that low of % then that means other ones have a very high % -- meaning low selectivity |
|
|
|
fiffreak150
Starting Member
4 Posts |
Posted - 2010-10-05 : 11:53:58
|
| So, not entirely sure if the problem is resolved but I did find something interesting that fixes my problem.running my query, or a select *, or select count(*) with the where conditional being aflg= 'a' (both upper and lowercase) results in a table scan. however I displayed estimated execution plans for other characters and the plan used the index I had created. not sure why SQLServer seems to dislike 'a' but if my querys run correctly I'm not going to complain. Thanks for all the suggestions! |
|
|
|
|
Query Plan not using Index