November 21, 2003 at 12:21 pm
Hello All,
I was once told that the order of the predicates or conditions in a where clause could increase the performance of a query. Specifically, the last condition in a WHERE clause was evaluated first and then worked back to the WHERE keyword. Therefore you should put the condition that reduces the record set the greatest as the last condition in the WHERE clause.
Example 1 (faster?):
SELECT *
FROM Orders
WHERE OrderDate > '11/1/03' and CompanyId = 123
Example 2 (slower?):
SELECT *
FROM Orders
WHERE CompanyId = 123 and OrderDate > '11/1/03'
It was explained to me that Example 1 was faster because the "CompanyId = 123" condition would be evaluated first which returned a subset of records where the companyId = 123. SQL would then use that subset to perform the "OrderDate > '11/1/03'" condition.
The reasoning is that what ever condition returned the smallest subset should be the last condition in the WHERE clause.
Can anyone verify this? Is this documented anywhere? If I didn't explain myself well enough, let me know.
Any help you can provide would be great!!
Pat B
Pat Buskey
November 21, 2003 at 2:05 pm
I would not believe that, anymore. It may have been true, but the optimizer is getting pretty smart. Kalen's had a couple of interesting articles digging into the optimizer in SQLMag recently, check them out.
Although there are a couple of situations where I've been able to make better decisions. And one I haven't figured out, a purchased app has duplicate indexes on many tables. The same field (only that field) defined in two indexes. One clustered, one not. For some reason the optimizer seems to always choose the non clusterd one ????
There are some tricks you can play with SQL to improve the performance, but many of them make reading, and modifying the code more difficult.
Like "< 5 and > 5" is significantly faster than "not equal 5", (at least in 7, I haven't testd it on 2k).
KlK, MCSE
KlK
November 21, 2003 at 3:23 pm
The SQL Server query optimizer does re-order the predicates for best performance; that's one use for statistics. It also expands "<>" operators to sargable operations when possible in SQL Server 2000.
--Jonathan
--Jonathan
November 24, 2003 at 5:38 am
I guess u can test this in the query analyzer of SQL 2000.
In the query tab u have an execution plan which shows the efforts.
Kind regards J
JV
JV
November 24, 2003 at 5:57 am
Thank you all for you thoughts.
Pat
Pat Buskey
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply