February 9, 2005 at 6:47 pm
Does the order of columns in the where clause have an effect on performance. For example mytable has an index on col_A. Is there a difference in these two statments?
Statement 1.
Select * from mytable
Where col_a = 1 And col_b = 2
Statement 2.
Select * from mytable
Where col_b = 2 and col_a = 1.
Again, for the purpose of these two statements assume this is an index on col_a but no index on col_b.
Thanks - ron
February 9, 2005 at 6:55 pm
February 11, 2005 at 2:25 am
AFAIK, SQL Server 2000 optimizer takes care of that, and the order of conditions shouldn't affect results. It can make difference whether you put certain table into FROM clause or JOIN it; there is a diffrence between placing a condition as part of outer join or into the WHERE clause... but the order of conditions doesn't play any role (at least to my best knowledge).
February 11, 2005 at 9:01 am
>>Does the order of columns in the where clause have an effect on performance.
No. To see for yourself, paste the 2 queries into Query Analyser (QA) and type CTRL-K (equivalent to menu:Query->Show Execution Plan).
SQL tells SqlServer what you want, not how to get it - how to get it is the optimizer's job.
February 11, 2005 at 10:42 am
The SQL Server optimizer is a 'cost' based optimizer. Basically it will attempt to select the cheapest method (in physical and logical I/O) to satisfy your query (absent any optimizer hints). So ordering of where clauses, placement of variables and literals on where clauses really have no affect. Now if it were a 'rule' based optimizer like Oracle, then everything I just stated is reversed.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply