SQL: Optimizing Recursive CTE

Question

Example table structure:

EmployeeId  TeamleaderId  TopTeamleaderId   LEVEL   ParentTree    CompanyId  
1           0             0                 0       NULL          1
2           1             1                 1       2>1           1
3           2             1                 2       3>2>1         1

TeamleaderId is foreignKey reference to EmployeeId in the same table

Goal: Whenever a row is inserted in the table with EmployeeId, TeamleaderId, CompanyId automatically populate TopTeamleaderId, LEVEL and ParentTree with AFTER INSERT trigger

Code:

WITH CTE AS (
  SELECT EmployeeId, TeamleaderId,0 AS [Level], CAST(EmployeeId AS varchar(100)) AS Heirarchy, TopTeamleaderId
  FROM dbo.Employee
  WHERE EmployeeId IN (SELECT EmployeeId FROM Employee WHERE TeamleaderId IS NULL 
    AND CompanyId IN(SELECT DISTINCT CompanyId FROM INSERTED))

  UNION ALL

  SELECT mgr.EmployeeId, mgr.TeamleaderId, CTE.[Level] +1 AS [Level], 
        CAST(( CAST(mgr.EmployeeId AS VARCHAR(100)) + '>' + CTE.Heirarchy) AS varchar(100)) AS Heirarchy, CTE.TopTeamleaderId
  FROM CTE
    INNER JOIN dbo.Employee AS mgr
      ON TaskCTE.EmployeeId = mgr.ParentTeamleaderId
)
UPDATE Employee SET [LEVEL] = TC.[LEVEL], ParentTree = TC.Heirarchy, TopTeamleaderId = TC.TopTeamleaderId
FROM dbo.Employee AS Employee
JOIN (SELECT * FROM CTE WHERE EmployeeId IN(SELECT DISTINCT EmployeeId FROM INSERTED) AND ParentTeamleaderId IS NOT NULL) TC
ON 
Employee.EmployeeId = TC.EmployeeId 

Problem: Imagine there are like 1000000 employees in a company, this query would take a long time to execute. How to optimize it so that only the parents of the inserted row are taken in to account?

Answer 1

Recursive CTE's are great, but as you can see the perfomance can suffer with larger hierarchies. It is my firm belief that there is no shame in temp tables.

The following will generate a 200K point hierarchy in 0.784 seconds.

Example

Select EmployeeId
      ,TeamleaderId
      ,Lvl=1
      ,TopTeamleaderId = 0   
      ,ParentTree=cast(EmployeeId as varchar(500)) 
      ,CompanyID
 Into  #TempBld 
 From  Employee 
 Where TeamleaderId is null

Declare @Cnt int=1
While @Cnt<=30     --<< Set Your Max Level
    Begin
        Insert Into #TempBld 
        Select A.EmployeeId
              ,A.TeamleaderId
              ,B.Lvl+1
              ,IIF(B.Lvl=1,B.EmployeeId,B.TopTeamleaderId)
              ,concat(A.EmployeeId,'>',B.ParentTree)
              ,A.CompanyID
         From  Employee A
         Join  #TempBld B on (B.Lvl=@Cnt and A.TeamleaderId=B.EmployeeId)
        Set @Cnt=@Cnt+1
    End

--Select * from #TempBld Order by ParentTree

Returns