May 26, 2009 at 12:19 pm
I am attempting to define a view that includes a recursive CTE so that the view can be joined to other tables but the performance is horrible. Replacing the view with a Multi-statement Table-valued Function with a cross apply has much better performance but uses more resources than simply coding the recursive CTE in-line with the rest of the SQL.
Below are various SQL Statements tried using the AdventureWorks demo database under SQL Server 2008 version 10.0.1300.13
Any tips ?
Performance will be very important as the real data has about 150,000 leaf rows and the depth is about 9 levels.
IO Statistics for the below SQL
**** STORED PROCEDURE EXECUTION ****
Table 'Contact'. Scan count 0, logical reads 14, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Employee'. Scan count 0, logical reads 14, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 2, logical reads 26, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
**** INLINE RECURSIVE CTE EXECUTION ****
(4 row(s) affected)
Table 'Worktable'. Scan count 2, logical reads 25, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Contact'. Scan count 0, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Employee'. Scan count 0, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
**** VIEW WITH RECURSIVE CTE EXECUTION ****
(4 row(s) affected)
Table 'Worktable'. Scan count 2, logical reads 7348, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Contact'. Scan count 0, logical reads 2634, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Employee'. Scan count 1, logical reads 2045, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
use AdventureWorks
go
SET STATISTICS IO OFF
GO
IFOBJECT_ID('HumanResources.Employee_Tree') IS NOT NULL
DROP VIEW HumanResources.Employee_Tree
GO
CREATE VIEW HumanResources.Employee_Tree
AS
WITH EMP_cte
( SubordinateId , EmployeeID, ManagerID, FirstName, LastName, Title, RecursionLevel)
AS (
SELECT e.EmployeeID AS SubordinateId
,e.EmployeeID, e.ManagerID, c.FirstName, c.LastName, e.Title, 0
FROM HumanResources.Employee e
INNER JOIN Person.Contact c
ON e.ContactID = c.ContactID
UNION ALL
SELECT EMP_cte.SubordinateId
,e.EmployeeID, e.ManagerID, c.FirstName, c.LastName, e.Title, RecursionLevel + 1
FROM HumanResources.Employee e
INNER JOIN EMP_cte
ON e.EmployeeID = EMP_cte.ManagerID
INNER JOIN Person.Contact c
ON e.ContactID = c.ContactID
)
SELECT * FROM EMP_cte
GO
SET STATISTICS IO ON
GO
PRINT '**** STORED PROCEDURE EXECUTION ****'
EXECUTE AdventureWorks.dbo.uspGetEmployeeManagers @EmployeeID = 87
GO
PRINT '**** INLINE RECURSIVE CTE EXECUTION ****'
DECLARE @EmployeeID INT
SET@EmployeeID = 87
;WITH EMP_cte
(EmployeeID, ManagerID, FirstName, LastName, Title, RecursionLevel)
AS (
SELECT e.EmployeeID, e.ManagerID, c.FirstName, c.LastName, e.Title, 0
FROM HumanResources.Employee e
INNER JOIN Person.Contact c
ON e.ContactID = c.ContactID
WHERE e.EmployeeID = @EmployeeID
UNION ALL
SELECT e.EmployeeID, e.ManagerID, c.FirstName, c.LastName, e.Title, RecursionLevel + 1
FROM HumanResources.Employee e
INNER JOIN EMP_cte
ON e.EmployeeID = EMP_cte.ManagerID
INNER JOIN Person.Contact c
ON e.ContactID = c.ContactID
)
SELECT*
FROMEMP_cte
GO
PRINT '**** VIEW WITH RECURSIVE CTE EXECUTION ****'
DECLARE @EmployeeID INT
SET@EmployeeID = 87
SELECT*
FROM HumanResources.Employee_Tree
WHERESubordinateId = @EmployeeID
SQL = Scarcely Qualifies as a Language
May 26, 2009 at 2:42 pm
It looks to me that the reason is that the view has to materialize all the rows and then apply the filter, while the TVF is applying the filter as part of the CTE, thus reducing recursions. The same would apply on
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply