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 '12 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 '12 at 22:44
  • What indexes do you have at this table? – ypercubeᵀᴹ Jun 4 '12 at 23:53
  • ItemID is primary key and there is non clustered index on itemid and rootid too. – njvds Jun 5 '12 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. – Mikael Eriksson Jun 5 '12 at 3:40

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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