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.
Schema :
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.