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  @dt DATETIME
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
           (emp_name,
            emp_id,
            boss_id)
SELECT emp_name,
       emp_id,
       boss_id
FROM   hierarchy
WHERE  emp_id = @emp_id
SET @rowcnt = @@ROWCOUNT
SET @last_row_id = 0
WHILE (@rowcnt != 0)
  BEGIN
    INSERT INTO #full_hierarchy
               (emp_name,
                emp_id,
                boss_id)
    SELECT hierarchy.emp_name,
           hierarchy.emp_id,
           hierarchy.boss_id
    FROM   #full_hierarchy fh,
           hierarchy
    WHERE  rowid > @last_row_id
           AND fh.boss_id = hierarchy.emp_id -- Recurisve query
    
    SET @rowcnt = @@ROWCOUNT
    
    SET @last_row_id = @@IDENTITY - @rowcnt
  END
SELECT emp_name,
       emp_id,
       boss_id
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 :(