Performance issue for recursive queries by CTE

Common Table Expression (CTE) is a new feature in SQL Server 2005. One of its use is that it can used to create recursive queries easily. However, the performance is not so attractive comapred with plain T-SQL.

Let’s take an example to show the hierarchical list of managers and the employees who report to them in AdventureWorks database.

The first is the query by CTE:
USE AdventureWorks;
GO
PRINT convert(nvarchar(100),GETDATE(),121)
SET NOCOUNT ON;
WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS
(
    SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel
    FROM HumanResources.Employee
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1
    FROM HumanResources.Employee e
        INNER JOIN DirectReports d
        ON e.ManagerID = d.EmployeeID
)
SELECT ManagerID, EmployeeID, EmployeeLevel
FROM DirectReports
ORDER BY EmployeeLevel,ManagerID,EmployeeID;
PRINT convert(nvarchar(100),GETDATE(),121)
GO

The second is implemented by plain T-SQL:
USE AdventureWorks
GO
PRINT convert(nvarchar(100),GETDATE(),121)
SET NOCOUNT ON

DECLARE @RowCnt int,@Level int
SELECT @Level=0,@RowCnt=0

DECLARE @DirectReports TABLE (  
     ManagerID int  NULL,
     EmployeeID  int NOT NULL,
     EmployeeLevel int NOT NULL)

INSERT @DirectReports (ManagerID, EmployeeID, EmployeeLevel)
SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel
FROM HumanResources.Employee
WHERE ManagerID IS NULL
SET @RowCnt=ISNULL(@@ROWCOUNT,0)

WHILE @RowCnt>0
BEGIN
 INSERT @DirectReports (ManagerID, EmployeeID, EmployeeLevel)
 SELECT T1.ManagerID,T1.EmployeeID, @Level+1
 FROM HumanResources.Employee T1
 INNER JOIN @DirectReports T2 ON T1.ManagerID=T2.EmployeeID AND T2.EmployeeLevel=@Level
 SET @RowCnt=ISNULL(@@ROWCOUNT,0)
 SET @Level=@Level+1
END
SELECT ManagerID, EmployeeID, EmployeeLevel
FROM @DirectReports A
ORDER BY EmployeeLevel,ManagerID,EmployeeID

PRINT convert(nvarchar(100),GETDATE(),121)
GO
Run the two queries multiple times, the first one always takes about 170 ~ 190ms, while the second takes only 13 ~ 20ms. The CTE is 10 times slower.

If you are going to migrate recursive queries to CTE, please be aware of the performance implications. It can be a killer of your server if you have heavy recursive query load.

 

Advertisements
This entry was posted in SQL server 2005. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s