0

I have a sql proc with some where conditions that look something like this:

AND (@p1 is null OR @p1 like '%' + cast(f1 as varchar(max)) + '%')

Obviously this is to allow the user to filter by a particular parameter if they wish, or not filter they pass in null. The odd thing I noticed today while trying to debug a query is that when I comment this line out the query runs dramatically faster even though @p1 was null.

If @p1 is null, I would have expected SQL to be smart enough to ignore the other OR conditions. Am I doing something wrong here or is there a better way to accomplish this so that SQL doesn't waste time with additional OR conditions when the first one is satisfied? Or is there a fundamental issue I don't understand here about how SQL retrieves the data that prevents it from doing so?

Samuel Neff
  • 73,278
  • 17
  • 138
  • 182
BVernon
  • 3,205
  • 5
  • 28
  • 64
  • You can use OPTION (RECOMPILE) after the query if you want to improve the performance. SQL Server recompiles the query time depending on the parameters new values. – Engin Sep 23 '15 at 00:07
  • 1
    This is a situation where dynamically generated SQL will improve performance. If `@p1` is null, exclude that condition entirely. Assuming you have a number of these conditions, modifying the SQL to just what you need to check will be much more efficient. – Samuel Neff Sep 23 '15 at 00:10
  • Generally, because compiling the query is expensive, a plan is created that will return correct results **no matter what the values for the variables are, and that plan reused**. So at a minimum the plan has to at least visit the tables or indexes with f1 field, where if you remove the clause altogether it can, _possibly_, generate plans using indexes that do not contain f1. To be faster. You'd have to look at the plans to see how they differ. – Shannon Severance Sep 23 '15 at 00:17
  • 2
    I don't think anyone here on SO could answer your question any better than [this article by Gail Shaw](https://www.simple-talk.com/sql/t-sql-programming/how-to-confuse-the-sql-server-query-optimizer/). Seriously, it's that good. – Dave Mason Sep 23 '15 at 02:02
  • @DMason Actually someone is about to :) Since everyone on here seems to think I'm totally misunderstanding SQL and that I shouldn't be able to bend SQL to my will in this area I finally reached out to a DBA friend I have and he explained to me how to write my query in a way that will make SQL understand to short-circuit the second condition. He'll be putting his answer on here soon. – BVernon Sep 23 '15 at 02:48
  • 1
    @DMason +1 for the excellent link anyhow though. – BVernon Sep 23 '15 at 05:27
  • Ok, so my buddy is being slow in posting an answer so I'll give the short of it here until he gets on. You have to write it like this: @p1 is null OR (@p1 is not null AND p1 like ...) – BVernon Sep 24 '15 at 23:08

2 Answers2

3

The exection plan is based on the query, the actual value of @p1 is not considered when creating the plan (or at least not considered to be the only values that the plan will be used for).

If the database would decide that the condition should be short circuited, then it would have to actually evaluate the condition for each record even if the second part of the condition could use an index and eliminate records much more efficiently.

Usually a query is turned into something that is generally much more efficient than evaluating an expression for each record. That can however turn out to be less efficient for some specific values.

Guffa
  • 687,336
  • 108
  • 737
  • 1,005
  • I have 2 responses to that. The first one: As a matter of implementation, that 'might' be true (I don't know that it is). But as a matter of logic it is not. There is no reason that it shouldn't be able to tell that @p1 will hold the same value for every record and therefore only needs to be evaluated once. – BVernon Sep 23 '15 at 00:11
  • 2nd response: OK, so why can't it see that evaluating a check for a null value is far faster than evaluating a like pattern? I'm fine with it having to evaluate that check for null on every record because that's extremely fast. – BVernon Sep 23 '15 at 00:12
  • And sorry, not trying to bee too contrary but when I see all the other far more complex optimizations that SQL can make, these answers just don't quite satisfy me because this is a ridiculously simple calculation to make in comparison. – BVernon Sep 23 '15 at 00:14
  • 1
    @BVernon: You are thinking of the expression as something that should be evaluated, but the query planner doesn't do that. It doesn't think of the expression as something that should be short circuited or not, it turns the entire query inside out into something completely different to try to find the most efficient way to find the result. Even if the null check is efficient for one specific value, there is a tremendous number of values where it is useless. – Guffa Sep 23 '15 at 00:25
  • I understand that there is no requirement for SQL to short-circuit, but you are making it sound like it never would. Does it not look at indexes and make decisions on what order to operate based off of that? I guess I'm trying to understand how this is so different. – BVernon Sep 23 '15 at 00:53
  • @BVernon: To make a useful short circuit, it would have to build two different execution plans and choose between them depending on the parameter values. A query only has one execution plan. – Guffa Sep 25 '15 at 18:42
  • Guffa, see my comments on the post. I have an awesome DBA friend who figured out how to write the query so that SQL will short circuit the expression. I'm waiting for him to post his answer, but in the meantime you can get the gist of it from my comment. – BVernon Oct 04 '15 at 00:55
2

Because SQL is declarative, not procedural. Therefore, order of execution is not controlled by the user and isn't indicated by the order the statement is written. You describe what you want and the RDBMS decides how best to satisfy your request.

Bacon Bits
  • 30,782
  • 5
  • 59
  • 66
  • I actually know that. To be more specific I should have asked why isn't SQL smart enough to choose to operate on the first condition in this scenario, and if it is in fact operating on the first condition first then why is it also operating on the second condition? – BVernon Sep 22 '15 at 23:46
  • 2
    Or to put it much more simply: Why isn't the RDBMS deciding to satisfy my request in a more efficient manner? :) – BVernon Sep 22 '15 at 23:49
  • @BVernon This might be due to various reasons, including [parameter sniffing](http://blogs.technet.com/b/mdegre/archive/2012/03/19/what-is-parameter-sniffing.aspx) or [general nature of your query](http://stackoverflow.com/q/9136722/11683). – GSerg Sep 22 '15 at 23:56