Optimizing T-SQL query which constracts the same subtable twice

Question

I want to optimize this query

WITH CTE AS
(
    SELECT 
       KladrItemName     _KladrItemName
     , WordPositionKladrItem   _WordPositionKladrItem
     , WordPositionAddressString  _WordPositionAddressString
     , StartPosition     _StartPosition
     , EndPosition     _EndPosition
     , Metric      _Metric
     , IsConstruction    _IsConstruction
     , WordsCount     _WordsCount
     , Indeces      _Indeces
     , WordPositionAddressString  _StartWordIndex
     , WordPositionAddressString  _EndWordIndex
     , 1        _StepNumber
    FROM 
     (
     SELECT 
      dbo.tKladrItems.KladrItemName
      , dbo.tFoundWords.WordFromAddressString 
      , dbo.tFoundWords.WordFromKladr 
      , dbo.tFoundWords.WordPosition AS WordPositionAddressString
      , dbo.tWordKladrItems.wordNumber AS WordPositionKladrItem
      , dbo.tFoundWords.StartPosition
      , dbo.tFoundWords.EndPosition
      , dbo.tFoundWords.Metric
      , dbo.tFoundWords.IsConstruction
      , dbo.tKladrItems.WordsCount
      , dbo.tKladrItems.Indeces
     FROM
      dbo.tWordsFromKladr JOIN dbo.tWordKladrItems ON dbo.tWordsFromKladr.ID = dbo.tWordKladrItems.wordID 
      JOIN dbo.tFoundWords ON dbo.tFoundWords.WordFromKladr = dbo.tWordsFromKladr.WordFromKladr
      JOIN dbo.tKladrItems ON dbo.tWordKladrItems.kladrItemID = dbo.tKladrItems.id 
     )T
    UNION ALL
    SELECT
       KladrItemName
     , WordPositionKladrItem
     , WordPositionAddressString
     , CASE WHEN StartPosition  _EndPosition THEN EndPosition ELSE _EndPosition END -- MAX
     , CAST(Metric + _Metric AS numeric(20, 10))
     , IsConstruction + _IsConstruction
     , WordsCount
     , Indeces
     , CASE WHEN _StartWordIndex  WordPositionAddressString THEN _EndWordIndex ELSE WordPositionAddressString END
     , 1 + _StepNumber
    FROM 

     (
     SELECT 
      dbo.tKladrItems.KladrItemName
      , dbo.tFoundWords.WordFromAddressString 
      , dbo.tFoundWords.WordFromKladr 
      , dbo.tFoundWords.WordPosition AS WordPositionAddressString
      , dbo.tWordKladrItems.wordNumber AS WordPositionKladrItem
      , dbo.tFoundWords.StartPosition
      , dbo.tFoundWords.EndPosition
      , dbo.tFoundWords.Metric
      , dbo.tFoundWords.IsConstruction
      , dbo.tKladrItems.WordsCount
      , dbo.tKladrItems.Indeces
     FROM
      dbo.tWordsFromKladr JOIN dbo.tWordKladrItems ON dbo.tWordsFromKladr.ID = dbo.tWordKladrItems.wordID 
      JOIN dbo.tFoundWords ON dbo.tFoundWords.WordFromKladr = dbo.tWordsFromKladr.WordFromKladr
      JOIN dbo.tKladrItems ON dbo.tWordKladrItems.kladrItemID = dbo.tKladrItems.id
     ) AS Tab JOIN CTE ON
      Tab.KladrItemName = CTE._KladrItemName
     AND Tab.WordPositionKladrItem > CTE._WordPositionKladrItem
     AND Tab.WordPositionAddressString >  CTE._WordPositionAddressString
)
SELECT DISTINCT 

      _KladrItemName  KladrItemName
    , _StartPosition  StartPosition
    , _EndPosition   EndPosition
    , _Metric    SumMetric
    , _IsConstruction  SumIsConstruction
    , _Indeces    Indeces

FROM 
    CTE
WHERE 
    _StepNumber = _WordsCount
    AND (_IsConstruction = 0 or (_IsConstruction = 1 and _WordsCount > 1))
    AND _EndWordIndex - _StartWordIndex + 1 = _WordsCount
option (maxrecursion 0) 

So that the table

SELECT 
      dbo.tKladrItems.KladrItemName
      , dbo.tFoundWords.WordFromAddressString 
      , dbo.tFoundWords.WordFromKladr 
      , dbo.tFoundWords.WordPosition AS WordPositionAddressString
      , dbo.tWordKladrItems.wordNumber AS WordPositionKladrItem
      , dbo.tFoundWords.StartPosition
      , dbo.tFoundWords.EndPosition
      , dbo.tFoundWords.Metric
      , dbo.tFoundWords.IsConstruction
      , dbo.tKladrItems.WordsCount
      , dbo.tKladrItems.Indeces
     FROM
      dbo.tWordsFromKladr JOIN dbo.tWordKladrItems ON dbo.tWordsFromKladr.ID = dbo.tWordKladrItems.wordID 
      JOIN dbo.tFoundWords ON dbo.tFoundWords.WordFromKladr = dbo.tWordsFromKladr.WordFromKladr
      JOIN dbo.tKladrItems ON dbo.tWordKladrItems.kladrItemID = dbo.tKladrItems.id

builds only once. But when I declare it as a temporary table, the execution time increases several times. Is there a way to optimize it by building this table once? Is there a way to optimaze it somehow else?

Thank you for help.

Answer 1

Use:

WITH base_table AS (
    SELECT wki.KladrItemName, 
           fw.WordFromAddressString, 
           fw.WordFromKladr, 
           fw.WordPosition AS WordPositionAddressString,
           tfk.wordNumber AS WordPositionKladrItem,
           fw.StartPosition,
           fw.EndPosition,
           fw.Metric,
           fw.IsConstruction,
           wki.WordsCount,
           wki.Indeces
      FROM dbo.tWordsFromKladr tfk
      JOIN dbo.tWordKladrItems wki ON wki.wordID = tkf.ID 
      JOIN dbo.tFoundWords fw ON fw.WordFromKladr = tfk.WordFromKladr
      JOIN dbo.tKladrItems ki ON ki.id = wki.kladrItemID),
     cte AS (
     SELECT bt.*
      FROM base_table bt
     UNION ALL
     SELECT KladrItemName, 
            WordPositionKladrItem, 
            WordPositionAddressString, 
            CASE WHEN StartPosition  _EndPosition THEN EndPosition ELSE _EndPosition END -- MAX, 
            CAST(Metric + _Metric AS numeric(20, 10)), 
            IsConstruction + _IsConstruction,
            WordsCount, 
            Indeces, 
            CASE WHEN _StartWordIndex  WordPositionAddressString THEN _EndWordIndex ELSE WordPositionAddressString END,
            1 + _StepNumber
       FROM base_table)
...

Answer 2

You can create two CTEs in a statement. Try this:

WITH Sub As
 (SELECT i.KladrItemName,
            f.WordFromAddressString, f.WordFromKladr,
            f.WordPosition WordPositionAddressString,
            wi.wordNumber WordPositionKladrItem,
            f.StartPosition, f.EndPosition, f.Metric, 
            f.IsConstruction, i.WordsCount, i.Indeces         
         FROM dbo.tWordsFromKladr w
            JOIN dbo.tWordKladrItems wi ON wi.ID = i.wordID                 
            JOIN dbo.tFoundWords f ON f.WordFromKladr = w.WordFromKladr
            JOIN dbo.tKladrItems i ON wi.kladrItemID = i.id ),
   CTE As
 (SELECT KladrItemName _KladrItemName, 
      WordPositionKladrItem _WordPositionKladrItem, 
      WordPositionAddressString _WordPositionAddressString, 
      StartPosition _StartPosition ,  
      EndPosition _EndPosition,
      Metric _Metric, IsConstruction _IsConstruction,
      WordsCount _WordsCount,
      Indeces _Indeces,
      WordPositionAddressString _StartWordIndex ,
      WordPositionAddressString _EndWordIndex,
      1 _StepNumber     
   FROM Sub T    
    UNION ALL    
 SELECT KladrItemName, WordPositionKladrItem, WordPositionAddressString,
      CASE WHEN StartPosition < _EndPosition 
            THEN EndPosition ELSE _EndPosition END, -- Max
      CAST(Metric + _Metric AS numeric(20, 10)),
      IsConstruction + _IsConstruction, 
      WordsCount, Indeces,
      CASE WHEN _StartWordIndex  WordPositionAddressString 
           THEN _EndWordIndex ELSE WordPositionAddressString END, 
      1 + _StepNumber
 FROM Sub Tab
    JOIN CTE                
       ON Tab.KladrItemName = CTE._KladrItemName        
          AND Tab.WordPositionKladrItem > CTE._WordPositionKladrItem        
          AND Tab.WordPositionAddressString >  CTE._WordPositionAddressString)
SELECT DISTINCT _KladrItemName KladrItemName,
    _StartPosition  StartPosition,  _EndPosition EndPosition,
    _Metric  SumMetric,_IsConstruction SumIsConstruction,
    _Indeces Indeces 
FROM CTE
WHERE_StepNumber = _WordsCount    
    AND (_IsConstruction = 0 or (_IsConstruction = 1 and _WordsCount > 1))        
    AND _EndWordIndex - _StartWordIndex + 1 = _WordsCountoption     
 (maxrecursion 0)