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 |
|
Curt Blood
Starting Member
23 Posts |
 Posted - 2008-07-17 : 17:27:23
|
I work with an out-of-the-box program that doesn't allow me to change queries or select statements. All I can do to try and optimize is run Profiler, see what statements are being run, and index as best I can.The query from hell is as follows (sorry I can't use the real table/column names):select * from Stuff where ((StuffSet = 'PLACE'))and (StuffNum in (select StuffNum from Stuff where StuffType in(select Value from Synonyms where SynID = 'STUFFTYPE' and SynValue = 'STUFF'))) OPTION (FAST 1000) Stuff has 163000 records, and indexes on (StuffNum,StuffSet) and (StuffSet,StuffNum). Obviously, the second index was created by me in desperation. Synonyms has ~100 records, and an index on (SynID, SynValue)The estimated execution plan shows a bookmark lookup, which I'm trying to avoid with a covering index, but I have no idea if there can possibly be an index that takes care of this. The Bookmark lookup is taking 95% of the Query cost.Thanks for any help, and I again apologize for the obfuscation. If there is a better way to obfuscate, let me know.Also, just out of curiosity, is it redundant to have an index on (columnA,columnB) as well as (columnB,columnA)?I'm going to check back in the morning -- serious need for beer...Thank youCurt |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-07-17 : 17:39:44
|
| A covering index would be pointless if you've got a clustered index since the query uses *. Does it really use *?If this were SQL Server 2005, then we could just add include columns to the existing index.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
Curt Blood
Starting Member
23 Posts |
Posted - 2008-07-18 : 08:51:25
|
| Yes, this application is a compendium of worst practices on the database side... Select *, client-side cursors, page locking (which gets interesting when you combine it with cursors and select *), you name it.One possible ray of sunshine is that there is not a clustered index on this table... would that make it possible to do an index that would help at all?Thanks for the reply, TaraCurt |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-07-18 : 12:33:32
|
| A clustered index should exist on most every table, there are rare exceptions to this best practice. Add a clustered index and then compare the old execution plan with the new one.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
|
|
|
|
|
index suggestion?