Recursive CTE taking too long to run

Question

question

rajanidesai-2545 asked Apr 29, '22 ErlandSommarskog answered Apr 29, '22

Recursive CTE taking too long to run

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)

sql-server-transact-sql

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 1 · 2022-04-29T02:02:10.4Z

LiHongMSFT-3908 answered Apr 29, '22 ErlandSommarskog commented Apr 29, '22

Hi @rajanidesai-2545
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.

· 2

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlafHelper-2800 · Apr 29 at 06:37 AM

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.

No, it don't, because of

WHERE e.ManagerEmployeeNumber = mgr.employeenumber

It's a so called "old style join", a deprecated syntax; one should avoid it and it will be removed.
https://www.red-gate.com/hub/product-learning/sql-prompt/finding-code-smells-using-sql-prompt-old-style-join-syntax-st001

0 ·

ErlandSommarskog OlafHelper-2800 · Apr 29 at 09:36 PM

It's a so called "old style join", a deprecated syntax; one should avoid it and it will be removed.

No, that is not deprecated. That is perfectly valid ANSI SQL.

The deprecated SQL the Red Gate article discusses is the outer join operators *= and =*, and they were removed entirely in SQL 2012, after only have been available in compat level 80 in SQL 2005 and SQL 2008.

0 ·

Answer 2 · 2022-04-29T12:23:16.263Z

TomPhillips-1744 answered Apr 29, '22

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.

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 3 · 2022-04-29T21:42:03.027Z

ErlandSommarskog answered Apr 29, '22

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.

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

question