Recursive CTE taking too long to run

rajani desai 1 Reputation point
2022-04-29T00:50:08.987+00:00
  • Hi All,

below is the recursive query where am trying to get no of employees under everyone beneath (direct reports and their direct reports, etc.)

main table has 39000 employees .

  • if i run for a one employee or for one manager the query run great but when i try to run entire table the query is taking too long to run and no results are displayed.

can you please help me with the query what am missing in query . am guessing query is going to infinite loop.

for Eg: given managernumber = 234565

employeenumber topmanager1 ----234565
222221 234565
608812 234565
619553 234565
424704 234565

201258 619553 topmanager2-----619553
623953 619553
647764 619553
424700 619553

employeenumber topmanager3-----647764
129452 647764
340822 647764
656814 647764
689540 647764
623433 647764

WITH EmployeeCTE
AS (SELECT e.employeenumber, e.ManagerEmployeeNumber as topmanager
FROM [dbo].[Employeetable ] e, [dbo].[employeetable] m
WHERE e.ManagerEmployeeNumber = m.employeenumber
UNION ALL
SELECT e.employeenumber, mgr.topmanager
FROM [dbo].[Employeetable ] e, EmployeeCTE mgr
WHERE e.ManagerEmployeeNumber = mgr.employeenumber)
SELECT *
FROM EmployeeCTE AS u

order by topmanager
OPTION (MAXRECURSION 0)

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,554 questions
0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. LiHong-MSFT 10,046 Reputation points
    2022-04-29T02:02:10.4+00:00

    Hi @rajani desai
    I noticed that you are using FROM [dbo].[Employeetable ] e, [dbo].[employeetable] m ，which means [dbo].[Employeetable ] cross join [dbo].[employeetable],and it will return the Cartesian product.
    Try this query: 

    ;WITH EmployeeCTE AS   
(  
 SELECT employeexrefcode, ManagerEmployeeNumber as topmanager  
 FROM [dbo].[employeetable]  
 UNION ALL  
 SELECT e.employeexrefcode, mgr.topmanager  
 FROM [dbo].[employeetable] e JOIN EmployeeCTE mgr ON e.ManagerEmployeeNumber = mgr.employeexrefcode  
)  
SELECT *  
FROM EmployeeCTE   
ORDER BY topmanager

    In addition, you can also optimize query speed by adding appropriate indexes. In this sitution,you could add indexes on employeexrefcode and ManagerEmployeeNumber columns.

    Best regards,
    LiHong

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.
  2. Tom Phillips 17,716 Reputation points
    2022-04-29T12:23:16.263+00:00

    Most likely because you have "MAXRECURSION 0" you have an endless loop.

    You should set that to a reasonable value. If you are getting a max recursion error, it is most likely a data issue you need to look at.

    1 person found this answer helpful.
    0 comments
  3. Erland Sommarskog 101.4K Reputation points MVP
    2022-04-29T21:42:03.027+00:00

    As Tom says, MAXRECURSION 0 does not belong here. The default value is 100, and I doubt that you have and organizational tree with more than 100 levels.

    So remove that hint. SQL Server will tell you if you hit the limit.

    If you do that, it could be because there are cycles in the data.

    However, the full query seems dubious. Usually in a recursive query you start with a certain level and work in one direction. But you are starting on all levels but the top level and work upwards. I think your base query needs a condition so that it includes the people at the bottom, that is, those who are not anyone's manager.

    0 comments