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.