Query Optimization TSQL and otherwise

Question

Assume I've a sql statement like the following with the variable @FOO set somewhere earlier in code:

SELECT FIELDLIST 
FROM TABLE 
WHERE 
(FIELD = @FOO OR @FOO IS NULL)

Is the query optimizer smart enough to do the second side of the OR first (@FOO IS NULL) because (another assumption) it is faster to do a null check than it is to do a field comparison?

I've done some informal benchmarking and not only do I not see a difference, I get different result times on my attempts which throws off the ability to do a proper comparison.

Answer 1

It's smart enough to apply the faster condition first, assuming it can judge which comparison will be faster in the general case. In this instance, the NULL check will almost always be faster because it has to compare at most one byte from each side of the expression and can factor it out.

Answer 2

Try testing it with the clauses in the opposite order:

SELECT FIELDLIST 
FROM TABLE 
WHERE 
(@FOO IS NULL OR FIELD = @FOO)

You may find that the first test will short-circuit the second, but not vice-versa.

Answer 3

One factor that may be in play here if this query is inside a sproc is "parameter sniffing". This can lead to inconsistent query response times. To fix this declare an internal variable in your sproc and assign this variable to the parameter value and then use the internal variable in your where clause or use the RECOMPILE clause in your sproc. There are many links on this subject.

Answer 4

The short answer...

Yes, the optimiser is smart enough.

The longer answer...

SQL is declarative rather than imperative: Your query is a description of the criteria that your results must meet, it is not step-by-step instructions on how to generate those results.

The optimiser executes the query in the most efficient order. It doesn't guarantee to evaluate your clauses in any particular order, or even evaluate them at all - if it can get the correct results without evaluating a particular clause then why would it bother?

The actual evaluation order of any particular query is an implementation detail and can change over time (eg, as the statistics on the table change).

In practice, the optimiser will occasionally get things wrong, but in this particular case - comparing a variable to NULL versus reading from a table or index - I don't think there's much chance of it screwing up, although you may want to consider using OPTION(RECOMPILE) or OPTION(OPTIMIZE FOR ...).

Answer 5

In my experience, sometimes is even faster to use two queries and an "UNION" instead of an "OR" clause.

SELECT FIELDLIST 
FROM TABLE 
WHERE 
(FIELD = @FOO)

UNION

SELECT FIELDLIST 
FROM TABLE 
WHERE 
(@FOO IS NULL)

This approach has the drawback of duplicating the SELECT statement, but the 1500% performance increase was justifying it. Of course, this depends on the database structure (in my case it was quite bad, and I could not change it).