Optimization in WHERE clause seems to be not working with UNION

user_8370346 0
Viewed 63

I have tried an SQL script in the following format. (There are multiple joins to produce set1, set2, and set3 but removed them for simplicity).

SELECT * FROM (
    SELECT * FROM Set1
    WHERE someConditions
    UNION
    SELECT * FROM Set2
    WHERE someConditions
    UNION 
    SELECT * FROM Set3
    WHERE @include = 1 AND otherConditions..
) as t
ORDER BY ...

One would expect that when @include = 0, the entirety of the last UNION will be ignored. This is also what I have seen when looked up for conditionally doing UNION.

But this is not the case for me; the query takes a long time to execute even when @include = 0. If I comment-out the last section the query will execute much faster.

SELECT * FROM (
    SELECT * FROM Set1
    WHERE someConditions
    UNION
    SELECT * FROM Set2
    WHERE someConditions
    --UNION 
    --SELECT * FROM Set3
    --WHERE @include = 1 AND otherConditions..
) as t
ORDER BY ...

Why the compiler is not able to pick this up? Is there any way to optimize queries in such scenarios?

sql-server-2014 sql-server sql
1 Answers

For me, OPTION(RECOMPILE) worked well on a simiilar test set-up - reusing the poor plan without the hint (assuming previously run with @include = 1) but creating a new, better plan when using RECOMPILE.

If that doesn't work for you, you could try splitting into two distinct statements using IF, eg:

BEGIN
IF @include = 1 
BEGIN

    SELECT * FROM Set1
    WHERE someConditions
    UNION
    SELECT * FROM Set2
    WHERE someConditions
    UNION 
    SELECT * FROM Set3
    WHERE otherConditions..

END

ELSE 
BEGIN
    SELECT * FROM Set1
    WHERE someConditions
    UNION
    SELECT * FROM Set2
    WHERE someConditions
END
END
user_119560890
Related