Need help with recursive CTE performance. Below CTE is running very slow as it is trying to pull heirarchical data recusively. Table is big with every root id having upto 3 recursive itemid. There could be around 200000 or more root id's. I know recursive CTE's are slow for huge dataset as for every rootid in anchor it would itemid recursively.
Create table RootItem (ItemId int primary key, RootIt int , insertdate datetime)
Above table have more than 1 million rows.
CTE query :
; With rootcte as ( select itemid from RootItem where rootid is null union all select r.itemid as RootId , i.itemid from RootItem i join rootcte r on i.rootid = r.itemid )
We can't modify table schema and use heirarchyid. I tried while loop too but that is slow too.
Is there any other way I can optimize this query ?
; With rootcte as ( select itemid from RootItem where rootid is null union all select r.itemid as RootId , i.itemid from RootItem i join rootcte r on i.rootid = r.itemid ) SELECT Cust.CustomerID , Cust.BusinessName , sCust.RegionCustomerID , ord.OrderID , ord.OrderItemID , prd.ProductCode , rc.itemid , rc.rootid , mf.FileID FROM vw_Customer Cust INNER JOIN SrcCustomer scust ON Cust.CustomerID = sCust.RegionCustomerID INNER JOIN OrderItem ord ON Cust.MasterCustomerID = ord.MasterCustomerID INNER JOIN Product ON ord.ProductID = Product.ProductID INNER JOIN rootcte rc ON ord.RootOrderId = rc.Rootid INNER JOIN MFolder mf ON mf.mfolderid = rc.itemid INNER JOIN MVersion mv ON mv.mfolderversionid = mf.mfolderid WHERE ord.IsActive = 1 and product.IsSelling = 1 and mf.fileid in (23,45,29) and mv.isdeleted = 'N'
I am also working with BI group to change query logic and filter data in cte itself of move couple of joins and criteria to cte.. Thanks for all the comments.