Ordering of WHERE clauses for SQL Server

Question

Can it make any difference to query optimisation to have WHERE clauses in a different order for SQL Server?

For example, would the query plan for this:

select * from table where col1 = @var1 and col2 = @var2

be different from this?:

select * from table where col2 = @var2 and col1 = @var1

Of course this is a contrived example, and I have tried more complex ones out. The query plan was the same for both, but I've always wondered whether it was worth ordering WHERE clauses such that the most specific clauses come first, in case the optimiser somehow "prunes" results and could end up being faster.

This is really just a thought experiment, I'm not looking to solve a specific performance problem.

What about other RDBMS's, too?

Answer 1

No, the query optimizer will find out anyway which index or statistics to use. I'm not entirely sure, but i even think that boolean expressions in sql is not evaluated from left to right but can be evaluated in any order by the query optimzer.

Answer 2

I don't think it'll make much of a difference.. What does make a difference in all the sql languages is the order in which you use sql functions.

For example: When you do something like this:

select title, date FROM sometable WHERE to_char(date, 'DD-MM-YYYY') > "01-01-1960"

Would go slower then this:

select title, date FROM sometable WHERE date > to_char('DD-MM-YYYY', %USERVALUE%)

This is because of the number of times the function needs to be evaluated.

Answer 3

Hello Neil,

every modern RDBMS has a query optimizer which is responsible, among other things, of reordering the conditions. Some optimizers use pretty sophisticated statistics to do this, and they often beat human intuition about what will be a good ordering and what will not. So my guess would be: If you can definitely say "This ordering is better than the other one", so can the optimizer, and it will figure it out by itself.

Conclusion: Don't worry about such things. It is rarely worth the time.

Cheers, Tom

Answer 4

Also, the ordering might make a difference in case you are using nested queries. The smaller the result set of the inner query, less scanning the outer query will need to do over it. Of course, having said that, I second what rsp said in the comment above - indexes are the main key in determining how long a query will take; if we have an index on the column, SQL Server would directly do a SEEK instead of SCANning the values and hence, the ordering would be rendered irrelevant

Answer 5

Readability for humans should be your only goal when you determine the order of the conditions in the where clause. For example, if you have a join on two tables, A and B, write all conditions for A, then all conditions for B.

Answer 6

SQL is "declarative" so it makes no difference. You tell the DBMS what you want, it figures out the best way (subject to cost, time etc).

In .net, it would make a difference because it's procedural and executed in order.