Wednesday, January 27, 2010

Recursive CTE vs temp table - Performance comparison

I ended last post discussing the query plan of a recursive cte. On this one, we will see the performance comparison of recursive cte based solution against a while loop based solution.

The table structure remains same with three columns emp_id,emp_name and boss_id.
The problem statement is, when given a employee id, the query/TSQL batch should traverse the hierarchy ladder and provide all the boss_ids upto the CEO/ the top most position.

The query used and the query plan obtained for a recursive cte are provided here .
If we were do the same thing using a conventional technique say using a while loop,
then the TSQL batch would look like

SET nocount  ON
DECLARE  @emp_id INT
DECLARE  @rowcnt      INT,
         @last_row_id INT
CREATE TABLE #full_hierarchy (
  rowid    INT    IDENTITY ( 1 , 1 )    PRIMARY KEY,
  emp_name VARCHAR(100),
  emp_id   INT,
  boss_id  INT)
SET @dt = Getdate()
SET @emp_id = 1
INSERT INTO #full_hierarchy
SELECT emp_name,
FROM   hierarchy
WHERE  emp_id = @emp_id
SET @rowcnt = @@ROWCOUNT
SET @last_row_id = 0
WHILE (@rowcnt != 0)
    INSERT INTO #full_hierarchy
    SELECT hierarchy.emp_name,
    FROM   #full_hierarchy fh,
    WHERE  rowid > @last_row_id
           AND fh.boss_id = hierarchy.emp_id -- Recurisve query
    SET @rowcnt = @@ROWCOUNT
    SET @last_row_id = @@IDENTITY - @rowcnt
SELECT emp_name,
FROM   #full_hierarchy
PRINT Datediff(ms,@dt,Getdate())
DROP TABLE #full_hierarchy

Query plan for the same is provided below.

The query plan that repeats at each recursive call is alone provided. I have a clustered index seek at the temp table and at hierarchy table which means the plan is pretty good. So when compared against the CTE based solution, we get the following results.

CTE wins hands down on performance when compared to temp table based solution.
But most important part is one needs to have the recursive part of the CTE indexed perfectly. Without the correct index on hierarchy table, I noticed that there was not much of a difference between CTE and temp table solution.So, the inference that we can draw out of these results is CTE uses indexes effectively and is definitively a better solution while solving recursion based problems.

PS: Shifting house last weekend. No internet connection @ home. So post delayed by 2 days :(


Uiop said...

May be you can optimize your temporary table with index and save time vs CTE....

Anvesh Patel said...

Nice Article !
This is my pleasure to read your article.
Really this will help to people of SQL Server Community.

I have also prepared one article about, Temporary Table vs Common Table Expression in SQL Server.
You can also visit my article, your comments and reviews are most welcome.