# question

## 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)

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

1 Vote"

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

· 2

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
OlafHelper-2800

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.

1 Vote"

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.

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