WHERE sequence and performance

Question

WHERE sequence and performance

Danie Swart-243670

SSC Rookie

Points: 38
More actions
July 5, 2005 at 9:11 am

#165244

Hi
Maybe someone here can help me with a question I have:
Does the "sequesnce" in which you do your WHERE clause impact on the performance in a query?
e.g.
Will there be any performance differences between:
SELECT this, that
FROM tbl
WHERE field1=1 ANDfield2='that'
and
SELECT this, that
FROM tbl
WHERE field2='that' AND field1=1
if so, can you please explain...
Thanx in advance
Composite index is only useful for a query when the WHERE clause of the query matches the column(s) that are leftmost in the index. If the index has been created for the columns (address_id, address_type) but the query is using only address_type the index will not be used by Query Optimizer. Basically saying, it is very difficult to guess properly for the composite index without seeing the actual queries.

May be you are right. If we specified all indexed columns (for an index), regardless their sequence order, there should be no difference in performance.

Thanks, dude.

Leo

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

No. Sql server picks up the best sequence to apply the where condition. You can't really have much control on that part (unless you use a case statement).

Leo Leong SSCommitted Points: 1615 More actions · Answer 2

hm ... i read something about this somwhere. It says if your first condition match with your first column in your index, it will fasten the speed to build the query plan (if i am not mistaken).

for example, you have an index consists of col2, col1, col3. In your WHERE clause, if you put "WHERE col2 = xxx and col3 = yyy and col1 = zzz", it will run faster. Second condition onwards can be ignore. However, if we follow the sequence, it may give us a little bit of performance gain too.

if you put "WHERE col1 = zzz AND col3 = yyy AND col2 = xxx", query optimizer is intelligent enough to determine the best plan that you shold take.

Please correct me if I am wrong, Remi.

Thanks.

Leo

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

hm ... i read something about this somwhere. It says if your first condition match with your first column in your index, it will fasten the speed to build the query plan (if i am not mistaken).

I would love to see that link...

The one thing about compound indexes is that if your where condition uses only the 2nd or third column, then the index becomes useless because an index scan is required instead of a seek (because the data is sorted by col1, col2, col3). This is why it can be usefull to have an index on last_name, first_name and another index on first_name only.

About the order in the query, the optimizer will just ignoring it and figure the fastest (correct) way to filter out the data.

Leo Leong SSCommitted Points: 1615 More actions · Answer 4

here is the link:

http://www.sqlservercentral.com/columnists/lPeysakhovich/indexcreationguidelines.asp

There is a line of statement as below:

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

That's exactly what I said... only in proper english . In the case of such an index, if you use only the right column (which is not really sorted), the server would have to do and index scan + bookmark lookup. It's most likely faster to just do a clustered index scan in such cases.