Temporary table vs short-circuit operation for SQL query

Question

Following is my query which I want to optimize, here the bottleneck for the optimization is CONDITION_B.

select @COMPUTE_X = count(distinct TABLEA.COLUMN_T5)
from #TMP_TABLEA TABLEA
inner join TABLEB on TABLEB.ID = TABLEA.ID
left join....
where
(
  CONDITION_A --Complex condition
) and
(
  CONDITION_B --Complex condition with some functions returning table
)

If I put my result of above query leaving CONDITION_B to a temporary table and then apply CONDITION_B on that temporary table, I achieve a very good amount of performance gain. I think CONDITION_B always evaluated whether the result for CONDITION_A is false.

Could anyone let me know if there is a better way of doing it. And does short-circuit operations work in SQL query statement, if yes what is the order of their processing.

SQL doesn't use short-circuit logic like most other programming languages; the optimizer is allowed to re-arrange to what it thinks best. You're also not guaranteed that it won't call the second statement regardless (ie, `WHERE a IS NOT NULL AND GET_RANDOM_NUMBER() > 1` _may_ call the function, regardless of `a`, and may call it first, before looking at `a` anyways). That said, it can _appear_ to operate as you expect, because it may be able to optimize away certain things (ie, if `a` is always null, and it knows ahead of time, it may not bother, because the condition would always be false). — Clockwork-Muse, Oct 16 '12 at 15:47
We may be better able to help you if you provide the entire statement, and (probably) the explain plan. What do your indices look like? — Clockwork-Muse, Oct 16 '12 at 15:49

score 1 · Accepted Answer · answered Oct 16 '12 at 15:44

The only way to enforce the order of evaluation is to use the CASE statement. Although it might not look pretty, the following might have similar performance:

select @COMPUTE_X = count(distinct TABLEA.COLUMN_T5)
from #TMP_TABLEA TABLEA
inner join TABLEB on TABLEB.ID = TABLEA.ID
left join....
where 1 = (CASE WHEN CONDITION_A then 1
                WHEN CONDITION_B then 1
                else 0
           end)

Remember, SQL is a descriptive language not a procedural language. You write the SQL to describe the results that you want. The order that you place things in a statement can be totally rearranged by the query optimizer. However, CASE statements are one exception. The order of evaluation is guaranteed in most cases (although not when aggregations are involved).

By the way, if you post another query with the full query, there may be other opportunities to speed the query.

Be careful if `condition` includes aggregates. Even `CASE` isn't guaranteed to short circuit in all scenarios. See http://dba.stackexchange.com/questions/12941/does-sql-server-read-all-of-a-coalesce-function-even-if-the-first-argument-is-no — Aaron Bertrand, Oct 16 '12 at 15:53
@AaronBertrand . . . a very good warning. I included that in the answer. — Gordon Linoff, Oct 16 '12 at 16:01

Temporary table vs short-circuit operation for SQL query

1 Answers1