SQL Server Performance Forum – Threads Archive

Duplicate index spool or not?

Hello. I have a question about the execution plan here:
http://students.info.uaic.ro/~vlad.cananau/img/plan.jpg 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:http://students.info.uaic.ro/~vlad.cananau/img/test.sql) — 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))
Don’t worry about efficiency, the query is not meant to be fast or uncostly, I just need to understand the plan. Thank you

---

IMO, since it cannot correlate, it makes sence to have at least 3 indexes separatly (you hardcoded the year) Did you try
;
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 — Don’t use a function on the column !(demotes indexusage)
WHERE OrderDate BETWEEN ‘2001-01-01’ AND ‘2003-12-31 23:59:59.997’
GROUP BY SalesPersonID, YEAR(OrderDate)
) SELECT emp.EmployeeID, Contact.FirstName, Contact.LastName
FROM HumanResources.Employee emp
INNER JOIN Person.Contact ON emp.ContactID = Contact.ContactID
WHERE
EXISTS (SELECT sp.SalesPersonID
FROM Sales.SalesPerson sp
INNER JOIN HumanResources.Employee
ON sp.SalesPersonID = Employee.EmployeeID
WHERE ManagerID = emp.EmployeeID)
AND NOT EXISTS (
SELECT sp.SalesPersonID
FROM Sales.SalesPerson sp
INNER JOIN HumanResources.Employee E
ON sp.SalesPersonID = E.EmployeeID
INNER JOIN yearlySales S1
ON S1.SalesPersonID = sp.SalesPersonID
WHERE ManagerID = emp.EmployeeID
AND NOT EXISTS (Select *
FROM yearlySales S2
Where S2.SalesPersonID = sp.SalesPersonID
AND S2.SalesYear = S1.SalesYear – 1
AND S1.TotalSales <= S2.TotalSales ))

---

]]>