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
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==nullis 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)?