Recursive CTE performance

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.

You say that the hierarchy gets modified. Presumably while this operation is running, there is some amount of blocking which is taking place then?

Even if the hierarchy is changing, are the roots for items changing?

Have you looked at the time it would take to just make the mapping table from root to item and index it?

I would like to see the execution plan to see what is happening - the CTE should get spooled, but as a manually materialized and indexed table it might perform better in the later steps.

Even with heavy activity, it would seem to me that someone has to be blocked if DML operations are changing data which this process is reading.

So I'd strongly consider taking a snapshot of the hierarchy.

In addition, you have a number of other INNER JOINs - you should review whether it is, in fact, the CTEs at all and whether there are any indexes which are missing to make those joins effective. The execution plan should tell you that.

You appear to have quite a few things in the WHERE clause which might help reduce some operations (and determine which indexes might be the best)), but it's hard to tell without looking at the execution plan or the indexes.

StackExchange Database Administrators Q#18805, answer score: 3