Force SQL Server to run query conditions as written?

Question

I'm using SQL Server 2008 R2 and I have this pseudo query (SP) :

select ...
from ...
WHERE    @LinkMode IS NULL
     AND (myColumn IN (...very long-running query...))
     ...
     ...

The problem is that the query takes a very long time to execute -- even if I execute the SP with @LinkMode=2.

As you noticed, the long-running query should be executed only if @LinkMode is null, which is not the case here. In my case @LinkMode = 2 !

However, if I change it to :

 select ...
    from ...
    WHERE    1=2
         AND (myColumn IN (...very long time exeted query...))
     ...
     ...

the SP does run fast.

I've heard before that sometimes the optimizer can optimize the order of criteria.

So I ask :

• Even if the optimizer chooses a different route, what can be faster than checking if =null? I mean, I think that checking if a==null is much faster than running the other long query...

• How can I force SQL Server to run the query as I've written it (the same order)?

Answer 1

You are falling into the "Catch-All Query" trap, which is explained very well by Gail Shaw here.

To summarize the problem: SQL Server optimizes away the significant overhead of query compilation by caching a query plan after compilation, and then later checking the cache for a matching query plan before a later compilation. The "matching" that occurs here is purely textual, so the actual value of a variable will not affect this.

That's good 99% of the time, but in some cases it's bad. One case where it's bad is when someone tries to construct a WHERE clause as though its like a short-circuiting IF statement in C, etc. This doesn't work well, because the SQL compiler has to make one query plan that will work regardless of what the parameter values actually are, and the only way that it can handle these "clever" logical switching-conditions in the WHERE clause is to make a simple brute-force plan that just scans the whole table, filtering the rows as it goes, without leveraging any indexes.

Not suprisingly, this makes them uniformly slow, no matter what the parameter/variable values are.

Answer 2

There is no guaranteed way to force SQL server to execute your clause conditions in a specific sequence. The optimizer will always evaluate them in the order it sees fit.

What you can do is something like this:

IF @LinkMode IS NULL
BEGIN
    select ...
    from ...
    WHERE (myColumn IN (...very long time exeted query...))
         ...
         ...
END
ELSE
BEGIN
    select ...
    from ...
    WHERE ...
         ...
END

Answer 3

If it is an option, use an IF statement to execute the appropriate form of the query. Also, in SQL, you tell the db engine what to do, not how to do it - things are not executed from beginning to end. It can be hard to predict what exactly it will do. You probably know this though ;)

Answer 4

Dynamic SQL would probably work too, since in that case the query optimizer should get the actual values at run-time (do correct me if I'm wrong, I'm actually not sure but seem to remember using it for similar situations). But I'm with the others in this one, in that an IF / ELSE clause would serve you best, as it's the simplest and easiest solution that will do exactly what's needed.

For future reference in case you haven't used it yet, a horribly ugly site with a working example for dynamic SQL can be found here for instance: http://sqlusa.com/bestpractices/dynamicsql/

Answer 5

I would recommend the IF/ELSE construct.. If for whatever reason that does not work for you, you can always consider using the WITH RECOMPILE option..