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
    , Cust.BusinessName  
    , sCust.RegionCustomerID  
    , ord.OrderID  
    , ord.OrderItemID  
    , prd.ProductCode  
    , rc.itemid
    , rc.rootid 
    , mf.FileID  
    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.

  • 2
    why do you need all of the hierarchies? SHouldn't there be a where clasue somewhere so you only calcluate for teh records you intend to use. Surely you don't need to build millions of hierarchies every time you run this.
    – HLGEM
    Jun 4, 2012 at 22:26
  • This is collateral report which is run around 5-6 times in a business hours and require to be run on whole dataset. I could have preload the data if data was static or not inserted often but in this case frequent DML operations are running on this table in DB.
    – njvds
    Jun 4, 2012 at 22:44
  • What indexes do you have at this table? Jun 4, 2012 at 23:53
  • ItemID is primary key and there is non clustered index on itemid and rootid too.
    – njvds
    Jun 5, 2012 at 0:07
  • 1
    You have to show the query you are actually using. As it is now, all you do is a complicated way of returning all ItemID from the table. The recursive CTE does not add any value. Jun 5, 2012 at 3:40

1 Answer 1


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.

  • Why would a DML operation block the SELECT? Is SQL Server still that limited?
    – user1822
    Jul 4, 2012 at 11:33
  • @a_horse_with_no_name msdn.microsoft.com/en-us/library/ms173763.aspx it's possible but the user mentioned there is high activity, so he would need to consider his strategy
    – Cade Roux
    Jul 4, 2012 at 13:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.