CODEWITHSUNDEEP
sqlsql-server-2012
BVernon
BVernon

Reputation: 3757

Why doesn't sql short circuit where conditions

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?

Upvotes: 0

Views: 146

Answers (2)

Guffa
Guffa

Reputation: 700572

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.

Upvotes: 3

Bacon Bits
Bacon Bits

Reputation: 32180

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.

Upvotes: 2

Related Questions