The four branches are identical and the Index Spools take in the same record set and output the same expressions. Also, no rebinding will be necessary during the entire query (as far as I can gather). My question is (and I know it may sound silly, but bare with the newbie here): does that plan say that there will be four identical temporary indexes created, one for each branch? Or is it that there will be a single spooled index which will be used on all 4 branches (and that would make perfect sense) but there's no other way for the execution plan to show it (as in, draw it)?
Here's a reduced version of my query:
(and full version here)
-- CTE here
WITH yearlySales (SalesPersonID, SalesYear, TotalSales) AS
(SELECT SalesPersonID, YEAR(OrderDate) as SalesYear, SUM(TotalDue) as TotalSales
FROM Sales.SalesOrderHeader
WHERE YEAR(OrderDate) BETWEEN 2001 AND 2003
GROUP BY SalesPersonID, YEAR(OrderDate)
)
-- Main statement
SELECT sp.SalesPersonID
FROM Sales.SalesPerson sp INNER JOIN HumanResources.Employee
ON sp.SalesPersonID = Employee.EmployeeID
WHERE
((SELECT TotalSales FROM yearlySales
WHERE SalesYear = 2003 AND SalesPersonID = sp.SalesPersonID)
<=
(SELECT TotalSales FROM yearlySales
WHERE SalesYear = 2002 AND SalesPersonID = sp.SalesPersonID)
OR
(SELECT TotalSales FROM yearlySales
WHERE SalesYear = 2002 AND SalesPersonID = sp.SalesPersonID)
<=
(SELECT TotalSales FROM yearlySales
WHERE SalesYear = 2001 AND SalesPersonID = sp.SalesPersonID))
The plan indicates that four identical temporary indexes are being created. This is what happens. CTE in the standard is supposed to provide more than just syntax level substitution. So if you use a particular query expression multiple times then the database engine is supposed to optimize the access patterns. But SQL Server does not currently do this and we do only syntax level substitution. Hence your query above has the plan you are seeing. You can simplify the query by doing below instead which is more efficient:
WITH yearlySales (SalesPersonID, SalesYear, TotalSales) AS
(
SELECT SalesPersonID, YEAR(OrderDate) as SalesYear, SUM(TotalDue) as TotalSales
FROM Sales.SalesOrderHeader
WHERE YEAR(OrderDate) BETWEEN 2001 AND 2003
GROUP BY SalesPersonID, YEAR(OrderDate)
),
p_yearlysales (SalesPersonID, [2001], [2002], [2003]) as
(
select SalesPersonID, [2001], [2002], [2003]
from yearlysales
pivot (max(TotalSales) for SalesYear in ([2001], [2002], [2003])) as p
)
-- Main statement
SELECT sp.SalesPersonID
FROM Sales.SalesPerson sp INNER JOIN HumanResources.Employee
ON sp.SalesPersonID = Employee.EmployeeID
JOIN p_yearlysales as p
ON p.SalesPersonID = sp.SalesPersonID
WHERE [2003] <= [2002] or [2002] <= [2001];
