January 13, 2007 at 8:33 am
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))
January 15, 2007 at 2:16 am
hi
As per execution plan,there are be four identical temporary indexes created. I guess this is problem with CTE. I always try to use CTE part as subquery.
Regards
Shrikant Kulkarni
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply
