none
Duplicate index spool or not? RRS feed

  • Question

  • Hello. I have a question about the execution plan here:

    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))
    Saturday, January 13, 2007 10:14 PM

Answers

  • 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];
    Sunday, January 14, 2007 12:58 AM