ANDALSO options, stop evaluating when one fails

Question

I have a SQL select statement that reads items. There are conditions for which items to display, but when one condition fails, I don't need to check the other.

For example: where item like 'M%' and item_class='B' and fGetOnHand(item)>0

If either of the first 2 fail, i do NOT want to do the last one (a call to a user defined function).

[From this SO question](https://stackoverflow.com/questions/46134872/is-ms-sql-and-or-conditional-perform-short-circuit-evaluation?noredirect=1&lq=1) it appears that both AND and OR do _not_ follow short circuiting behavior which we would expect to see in an app language like C# or Java. But, I will not mark this question duplicate, because you also are asking for a workaround to avoid calling the UDF. — Tim Biegeleisen, May 14 '19 at 15:29

score 2 · Accepted Answer · answered May 14 '19 at 15:36

From what I have read on this site, SQL Server's AND and OR operators do not follow short circuiting behavior. This means that the call to the UDF could happen first, or maybe not at all, should one of the other conditions happen first and fail.

We might be able to try rewriting your logic using a CASE expression, where the execution order is fixed:

WHERE
    CASE WHEN item NOT LIKE 'M%' OR item_class <> 'B'
         THEN 0
         WHEN fGetOnHand(item) <= 0
         THEN 0
         ELSE 1 END = 1

The above logic forces the check on item and item_class to happen first. Should either fail, then the first branch of the CASE expression evaluates to 0, and the condition fails. Only if both these two checks pass would the UDF be evaluated.

This is very verbose, but if the UDF call is a serious penalty, then perhaps phrasing your WHERE clause as above would be worth the trade off of verbose code for better performance.

PERFECT. query went from 25+ seconds to 4 by making the case statement — dcme, May 14 '19 at 19:47

ANDALSO options, stop evaluating when one fails

1 Answers1