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 checkingif 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)?