Optimize Query UNION ALL

by peixinho3   Last Updated April 20, 2018 09:06 AM

I have the above query:

DECLARE @ID INT
SET @ID = 12345

DECLARE @Field1 VARCHAR(MAX)
DECLARE @Field2 VARCHAR(MAX)
DECLARE @Field3 VARCHAR(MAX)



SELECT @Field1 = Field1,@Field2 = Field2 FROM Table1 WHERE ID = @ID

    SELECT CONVERT(VARCHAR(8),T.Date,112) AS Date,SUM(T.Test1) AS Test1,SUM(T.Test2) AS Test2,COUNT(DISTINCT T.Test3) AS Test3
    FROM (
        SELECT Date,CONVERT(INT,1) AS Test1,CONVERT(INT,0) AS Test2,t2.Test3
        FROM Table2 t2
        INNER JOIN (SELECT DISTINCT Test3 
                FROM Table2 t22 
                WHERE CASE WHEN LEN(@Field1) > 0 THEN t22.Field1 ELSE t22.Field2 END =
                CASE WHEN LEN(@Field1) > 0 THEN @Field1 ELSE @Field2 END) t ON t2.Test3 = t22.Test3 
        UNION ALL
        SELECT Date,CONVERT(INT,1) AS Test1,CONVERT(INT,0) AS Test2,t3.Test3
        FROM Table3 t3
        INNER JOIN (SELECT DISTINCT Test3 
                FROM Table3 t33 
                WHERE CASE WHEN LEN(@Field1) > 0 THEN t33.Field1 ELSE t33.Field2 END =
                CASE WHEN LEN(@Field1) > 0 THEN @Field1 ELSE @Field2 END) t ON t3.Test3 = t33.Test3 
        ) T
    GROUP BY CONVERT(VARCHAR(8),T.Date,112)
    ORDER BY CONVERT(VARCHAR(8),T.Date,112) DESC

My execution plan is the following:

enter image description here

I see that Table Spool has high cost. How can I optimize this query?

NOTE: Table3 have about 14 millions rows.



Related Questions


SQL Server Plan Guide

Updated March 03, 2017 05:06 AM

Queries without Good Enough Plan Found

Updated May 05, 2016 08:02 AM

Does SentryOne Plan Explorer work?

Updated February 21, 2017 18:06 PM


Estimated number of rows is inaccurate

Updated July 13, 2017 18:06 PM