# 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:

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

NOTE: Table3 have about 14 millions rows.

Tags :