SQL Server Performance Forum – Threads Archive
Order of conditions in WHERE clause
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
Check out this link elsewhere at performance.com http://www.sql-server-performance.com/transact_sql.asp There are several comments about where clauses, performance of, which type of operators work faster etc, and here is one section repeated in its entirety:
If a WHERE clause includes multiple expressions, there is generally no performance benefit gained by ordering the various expressions in any particular order. This is because the SQL Server Query Optimizer does this for you, saving you the effort. There are a few exceptions to this, which are discussed on this web site[7.0, 2000] Added 5-30-2003 To test if your particular one makes a difference, put both in QA and display the query plan. For this example, they are probably the same. Chris
I remmber that DB2 was concerned about this (at least a few versions back it was) as well as table order in JOINs would affect speed. I’m not sure this is true now. I don’t think this is true with SQL Server.
It was an issue in sql 6 and can still be an issue for those who dont use the ansi join standard and have their join criteria in the where clause(tsk,tsk,tsk). And there are other less common times when the order can have an effect. I havent come across any and even when I try, and look at the query plan, cant get it to perform differently based on the criteria. The only thing I do now, is make the where clauses as ‘readable’ as possible. So in the order that the conditions are thought about or the order that I do the joins in or any other order that makes sense (or just cause I feel like it).
Chris
Thank you all for your comments. Pat
]]>