CTE self join slow down the execution

Question

I am using the following query in SP.

DECLARE @DateFrom  datetime = '01/01/1753',       
     @DateTo   datetime = '12/31/9999'      
     BEGIN 
     WITH tmpTethers      
       AS      
       (      
        SELECT TL.str_systemid               AS SystemCode,       
          ISNULL(ml.name, ml.location)  AS [System],      
          TL.dte_created                AS [Date],       
          TL.str_LengthId               AS TetherRegId,       
          0                             AS LengthCut,      
          ISNULL(TL.dbl_newlength, 0)   AS LengthAdded,       
          CAST(0 AS FLOAT)     AS RemainingLength,      
          1 AS Mode,      
          UT.description      AS UOM      
        FROM OP_TetherLength AS TL      
          INNER JOIN master_location  AS ML  ON ML.location = TL.str_systemid      
          LEFT JOIN udc_type AS UT ON TL.lng_lengthuom = UT.udc      
        WHERE (TL.dte_dateadded BETWEEN  @DateFrom AND @DateTo)  


          
    UNION ALL      
          
    SELECT RR.systemcode     AS SystemCode,       
      ISNULL(ML.name, ML.location) AS [System],      
      RR.datecreated               AS [Date],       
      RR.oms_repairid              AS TetherRegId,       
      ISNULL(RR.cutlength, 0)      AS LengthCut,      
      0                            AS LengthAdded,       
      0              AS RemainingLength,      
      0 AS Mode,      
      UT.description      AS UOM      
    FROM Repair_Registration AS RR        
      INNER JOIN master_location AS ML ON RR.systemcode =  ml.location      
      LEFT JOIN udc_type AS UT ON RR.cutlength_uomid = UT.udc      
    WHERE --RR.cut_umbilical_tether = 0 AND      
  RR.cutbackrequired = 1 AND      
      (RR.datecreated BETWEEN  @DateFrom AND @DateTo)            
   ),      
         
   tmpOrderedTethers      
   AS      
   (      
    SELECT TOP 1000      
      SystemCode,       
      [System],      
      [Date],       
      TetherRegId,       
      LengthCut,      
      LengthAdded,       
      RemainingLength,      
      Mode,      
      UOM,      
      ROW_NUMBER() OVER(PARTITION BY SystemCode  ORDER BY [Date] ) AS RowNumber      
    FROM tmpTethers      
    ORDER BY SystemCode      
   ),      
         
   tmpFinalTethers      
   AS      
   (        
    SELECT SystemCode,       
      [System],      
      [Date],       
      TetherRegId,       
      LengthCut,      
      LengthAdded,       
      CASE      
       WHEN Mode = 1 THEN LengthAdded      
       ELSE 0 - LengthCut      
      END AS RemainingLength,      
      Mode,      
      UOM,      
      RowNumber      
    FROM tmpOrderedTethers      
    WHERE RowNumber = 1      
  
    UNION ALL      
      
    SELECT tmpOT.SystemCode,       
      tmpOT.[System],      
      tmpOT.[Date],       
      tmpOT.TetherRegId,       
      tmpOT.LengthCut,      
      tmpOT.LengthAdded,       
      CASE      
       WHEN tmpOT.Mode = 1 THEN /*tmpFT.RemainingLength +*/ tmpOT.LengthAdded      
       ELSE tmpFT.RemainingLength - tmpOT.LengthCut      
      END AS RemainingLength,      
      CASE      
       WHEN tmpOT.Mode = 1 OR tmpFT.Mode = 1 THEN 1      
       ELSE 0      
      END AS Mode,      
      tmpOT.UOM,      
      tmpOT.RowNumber      
    FROM tmpOrderedTethers AS tmpOT      
    INNER JOIN tmpFinalTethers AS tmpFT  ON tmpFT.SystemCode = tmpOT.SystemCode AND      
                tmpFT.RowNumber = tmpOT.RowNumber - 1      
   ),

  
 
   ---- FT - Previous      
   ---- OT - Current      
    
   SELECT SystemCode,       
     [System],      
     [Date],       
     TetherRegId,       
     LengthCut,      
     LengthAdded,       
     RemainingLength,      
     UOM,      
     RowNumber     
     ,ROW_NUMBER() OVER(PARTITION BY SystemCode  ORDER BY [Date] desc) AS SortNumber
   FROM tmpGetFinalTethers      
   ORDER BY SystemCode, SortNumber      
   OPTION (MAXRECURSION 1000)
END

In above query when I am commenting the following part then execution time reduced and data come fast:

SELECT tmpOT.SystemCode,       
          tmpOT.[System],      
          tmpOT.[Date],       
          tmpOT.TetherRegId,       
          tmpOT.LengthCut,      
          tmpOT.LengthAdded,       
          CASE      
           WHEN tmpOT.Mode = 1 THEN /*tmpFT.RemainingLength +*/ tmpOT.LengthAdded      
           ELSE tmpFT.RemainingLength - tmpOT.LengthCut      
          END AS RemainingLength,      
          CASE      
           WHEN tmpOT.Mode = 1 OR tmpFT.Mode = 1 THEN 1      
           ELSE 0      
          END AS Mode,      
          tmpOT.UOM,      
          tmpOT.RowNumber      
        FROM tmpOrderedTethers AS tmpOT      
        INNER JOIN tmpFinalTethers AS tmpFT  ON tmpFT.SystemCode = tmpOT.SystemCode AND      
                    tmpFT.RowNumber = tmpOT.RowNumber - 1

Please let me know how I can refine this.

user_18046084 · Answer

It seems like you have row by row processing in your [tmpFinalTethers] and [tmpGetFinalTethers] cte's.

Each row returned in [tmpFinalTethers] is based on [tmpOrderedTethers] and [tmpOrderedTethers]'s data is based on [tmpTethers]. Therefore the logic which contains in [tmpOrderedTethers] and [tmpTethers] will be executed n times, where n is a number of rows returned by [tmpFinalTethers].

The reason is because cte's are not materialized objects. They are not get stored in memory or disc, so they're executing each time you reference them outside of declaration.

Loading the resultset of [tmpOrderedTethers] to temp table may help if you really need row by row processing for your task and don't have other options.

Also it seems like your [tmpFinalTethers] and [tmpGetFinalTethers] have the same logic inside. I am not sure what the purpose for it. Mb you can do final select from [tmpFinalTethers] and get rid of [tmpGetFinalTethers].

Edited:

Try smth like this:

;WITH tmpTethers AS (...),
tmpOrderedTethers AS (...)
SELECT * INTO #tmpOrderedTethers FROM tmpOrderedTethers

;WITH tmpFinalTethers (
SELECT ... FROM #tmpOrderedTethers WHERE ...

UNION ALL

SELECT ... FROM #tmpOrderedTethers tmpOT INNER JOIN ...
)

Edited 2:

As you have OPTION (MAXRECURSION 1000) I suppose you always get 1000<= number of rows. For such amount of rows your solution with recursive cte combined with temp table will probably work. At least it would be better than cursor, because it consumes some resources in addition to row by row processing. But if you will need to process let's say 10 000 of rows then row by row processing is definitely not appropriate solution and you should find another one.

CTE self join slow down the execution

1 Answers