SQL Server Performance

Duplicate index spool or not?

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by VladIonut, Jan 13, 2007.

  1. VladIonut New Member

    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
  2. alzdba Member

    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 )
    )

Share This Page