Recursive Query SQL run too slow? how to improve?

Question

I am running a recursive query to build a hierarchy path for parent-child relationship. Problem is the query runs too slow. It takes 10hours and still not finish. My table EMPLOYEE has 40K rows. I am using SQL Server

Original Table

Employee	Manager
Luna	Jack
Japan	Jack
Alice	Luna
Alex	Luna
Jessica	Alex

Here is my desired table with column the path

Employee	Manager	Path
Jack	Null	Jack
Luna	Jack	Jack - Luna
Japan	Jack	Jack - Japan
Alice	Luna	Jack - Luna - Alice
Alex	Luna	Jack - Luna - Alex
Jessica	Alex	Jack - Luna - Alex - Jessica

My query

With emp as (
    select
      Manager as Employee,
      cast(Null as varchar(max)) as Manger,
      cast(Manager as varchar(max)) as path
    from Employee e1

    union all 

    select
      e2.Employee,
      cast(Manager as varchar(max)) as Manger,
      cast((emp.path + '-' + e2.Employee) as varchar(max)) as path
    from employee e2
    join emp on e2.Manager = emp.Employee
)
select *
from emp

Any idea how to improve code efficiency?

First work out your RDBMS; SQL Server and MySQL are *completely* different products. — Thom A, Jun 30 '22 at 20:59
Please read through this post https://meta.stackoverflow.com/a/271056/460557 which has a lot of good information on how to ask a SQL question here at Stack Overflow then edit your question accordingly. — Jorge Campos, Jun 30 '22 at 21:01
table structure (with indexes), and query is needed to find out what is wrong — Kadet, Jun 30 '22 at 21:01
And 10 hours for 40K rows sounds like a blocking problem, not a plan cost problem. Start by gathering the actual query plan in a test environment. — David Browne - Microsoft, Jun 30 '22 at 21:02
Use "execution plan" in SQL Server Management Studio. Example how to use [good answer](https://stackoverflow.com/questions/7359702/how-do-i-obtain-a-query-execution-plan-in-sql-server?answertab=scoredesc#tab-top) — Bushuev, Jun 30 '22 at 21:24
You can share the execution plan via https://pastetheplan.com. Please also show us the relevant table and index definitions. I'm guessing an index `(Manager, Employee)` is necessary — Charlieface, Jun 30 '22 at 21:32
When I select actual execution plan, Error "Showplan permission denied in database" — Suzie, Jun 30 '22 at 23:09
There is no index number, the actualy key use to join is manager_name_id (like jack.123) and employee_name_id ( like Luna@102) . I modified to name for reading convinient — Suzie, Jun 30 '22 at 23:11

John Cappelletti · Answer 1 · 2022-06-30T21:57:04.703

Recursive CTEs are great to a point. However, if you are looking to build a large hierarchy (like 200K+), there is NO shame in using TEMP tables.

Here is a stripped down/modified version I've used for my LARGE and SLOW MOVING hierarchies.

Example

Create Table #YourTable ([Employee] varchar(50),[Manager] varchar(50))
Insert Into #YourTable Values 
 ('Jack',null)
,('Luna','Jack')
,('Japan','Jack')
,('Alice','Luna')
,('Alex','Luna')
,('Jessica','Alex')


Select *
      ,Lvl=1
      ,Path=convert(varchar(500),Employee)
 Into  #TempBld 
 From  #YourTable 
 Where Manager is null

Declare @Cnt int=1
While @Cnt<=30  -- Set Max Level -- You can be a little generous here.
    Begin
        Insert Into #TempBld 
        Select A.*
              ,Lvl =B.Lvl+1
              ,Path=B.Path+' - '+A.Employee
         From  #YourTable A
         Join  #TempBld B on (B.Lvl=@Cnt and A.Manager=B.Employee)
        Set @Cnt=@Cnt+1
    End

Select * from #TempBld Order by Path

Results

Lvl Employee    Manager   Path
1   Jack        NULL      Jack
2   Japan       Jack      Jack - Japan
2   Luna        Jack      Jack - Luna
3   Alex        Luna      Jack - Luna - Alex
4   Jessica     Alex      Jack - Luna - Alex - Jessica
3   Alice       Luna      Jack - Luna - Alice

Thank John, problem is I cannot create new table, my role permission is connect & view data only — Suzie, Jun 30 '22 at 22:36

Recursive Query SQL run too slow? how to improve?

1 Answers1