Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Im using Sql server 2008 R2 And I have this pseudo query (SP) :

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

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

As you noticed , the long time consuming 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 , optimizer can optimize the order of criteria

So I ask :

  • Even if the optimizer choose 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)

share|improve this question
add comment

migrated from stackoverflow.com May 1 '13 at 19:10

This question came from our site for professional and enthusiast programmers.

5 Answers

up vote 18 down vote accepted

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.

share|improve this answer
    
Very interesting! Do you have any suggestions for what he's trying to accomplish though? An alternate to the short-circuit logic? –  mowwwalker May 2 '13 at 1:24
2  
Add the query hint OPTION (RECOMPILE) It tells Sql to recompile the query each execution. And because the query plan will never be reused, sql can optimize out the sub query. –  StrayCatDBA May 2 '13 at 2:15
    
@StrayCatDBA does it mean that my query will run as written ? (in my desire order ) ? –  Royi Namir May 2 '13 at 4:17
    
Yes, it will run as written. It doesn't change the order per se, it eliminates redundant or un-necessary comparisons. "A>7 AND A>5" --> "A>5" The A>7 is thrown away. It's static code analysis, not short circuiting. –  StrayCatDBA May 2 '13 at 11:11
    
I believe that the article that I pointed to by Gail Shaw goes into some of the possible solutions, but if you wish I can delineate what the possible solutions are in my response above. –  RBarryYoung May 2 '13 at 11:42
add comment

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
share|improve this answer
add comment

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 execute from beginning to end. It can be hard to predict what exactly it will do. You probably know this though ;)

share|improve this answer
add comment

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..

share|improve this answer
    
Could you maybe elaborate on what the "if/else construct" might look like? :D –  jcolebrand May 1 '13 at 23:13
    
I was going to suggest using OPTION (WITH RECOMPILE), as that would generate an ideal plan each time - the compile delay would add overhead, but I suspect it's better overall in this case. –  SqlRyan May 2 '13 at 1:23
add comment

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/

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.