Index Performance Question - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Index Performance Question

Author

Topic

3badi
Starting Member

2 Posts

Posted - 2006-12-24 : 18:26:42

Hello all,

if i have an index composed of 3 col. i have the following questions relating to the index performance:
1-if i need the 1st and 3rd col. to be included in the search criteria. What should i do with the 2nd one to gurentee the performance will not decrease?
2-Should i mention the 3 cols in same index order to get maximum benefit from index or i can use any order?

also I'd like to know the factors where index of multi-column is better than using same fields as single indexes or vice versa.
Thanks..

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-25 : 05:59:58

1. You can try something like
Where Col1 = @col1 and Col2 = Col2 and Col3 = @col3

2. Yes, you should specify columns in the same order in the Where clause as they were defined in the index definition. (in case of composite index)

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Kristen
Test

22859 Posts

Posted - 2006-12-26 : 13:22:09

1. I don't reckon it makes any difference, just do Col1 = @col1 AND Col3 = @col3. Provided "Clo1" is sufficiently "selective" the index will be used. If not change the order of the columns in the index to put the most selective first.

Try it various ways and see what query plan you get for each, and then use the highest performance method. And if that fails use an INDEX HINT (but that is really a last-ditch approach IMHO)

2) Again, I don't think it makes any difference. But try it and check the query plan.

Kristen

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-12-27 : 06:14:26

Personally I don't think that the Col2 = Col2 would make any difference because it's not selective at all and I would think that the query optimizer omits it completely. Worth a shot though!

The order of where/join-conditions shouldn't matter but I always put them in the indexed order anyway as I feel this is good practice and keeps the code "clean". That way the code looks the same always. As far as I can see you have 2 options; create a new index or rearrange the one you allready have (if the performance hit by using the one you have is too great of course).

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

Kristen
Test

22859 Posts

Posted - 2006-12-27 : 07:37:44

"I always put them in the indexed order"

Do you re-edit all your queries if you change order of Keys in the Index then?