question

rajanidesai-2545 avatar image
0 Votes"
rajanidesai-2545 asked ErlandSommarskog answered

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

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

LiHongMSFT-3908 avatar image
1 Vote"
LiHongMSFT-3908 answered ErlandSommarskog commented

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

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

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 Votes 0 ·

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 Votes 0 ·
TomPhillips-1744 avatar image
1 Vote"
TomPhillips-1744 answered

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

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

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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

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