title: Optimizing SQL-Query using CTE
body: <p>I have written the following (simplified) query:</p>
  <pre class="lang-sql prettyprint-override"><code>;WITH 
  PreAggregated AS
  (
      --Group by including ClassId and MeasureId
      SELECT    
                  [ColumnA],[ColumnB],[ColumnC],[ClassId],[MeasureId]                                                 
                  , SUM(a.[NonAggregatedValues]) as PreAggregatedValues                                          
      FROM  [TableA] a
                  INNER JOIN [TableB] b on b.PrimaryKey = a.ForeignKey1
                  INNER JOIN [TableC] c on c.PrimaryKey = a.ForeignKey2
      WHERE a.ReportDate = @reportDate AND c.TechID IN ([...])
      GROUP BY 
                  [ColumnA],[ColumnB],[ColumnC],[ClassId],[MeasureId]
  ),
  AllCombinations AS
  (
      --No values aggregated, just gets all possible combinations and is then left joined to [A],[B],[C],...
      SELECT [ColumnA],[ColumnB],[ColumnC] FROM PreAggregated a
      WHERE TechID IN ([...]) AND ReportDate = @reportDate
      GROUP BY [ColumnA],[ColumnB],[ColumnC]
  ),
  A AS
  (
      --Pivot for all ClassId = 5 using MeasureId 1-3
      SELECT [ColumnA],[ColumnB],[ColumnC],SUM([1]) [1],SUM([2]) [2],SUM([3]) [3]
      FROM (
                  SELECT [ColumnA],[ColumnB],[ColumnC],[ClassId],[MeasureId] 
                  FROM   PreAggregated
                  WHERE [ClassId] = 5 AND TechID IN ([...]) AND ReportDate = @reportDate) as [DATA]  
                  PIVOT(
                             Sum([PreAggregatedValues])
                             FOR MeasureId IN ([1],[2],[3])) AS PIVOT_DATA
      GROUP BY [...]
  ),
  B AS
  (
      --Pivot for all ClassId = 6 using MeasureId 1-3
      SELECT [ColumnA],[ColumnB],[ColumnC],SUM([1]) [1],SUM([2]) [2],SUM([3]) [3]
      FROM (
                  SELECT [...] 
                  FROM   PreAggregated
                  WHERE [ClassId] = 6 AND TechID IN ([...]) AND ReportDate = @reportDate) as [DATA]  
                  PIVOT(
                             Sum([PreAggregatedValues])
                             FOR MeasureId IN ([1],[2],[3])) AS PIVOT_DATA
      GROUP BY [...]
  ),
  C AS
  (
      --Pivot for all ClassId = 7 using MeasureId 1-3
      [...]
  )
  
  SELECT                       
          AllCombinations.[x],
          AllCombinations.[y],
          [...],
          A.[1] [A_1],
          A.[2] [A_2],
          A.[3] [A_3],
          B.[1] [B_1],
          B.[2] [B_2],
          B.[3] [B_3],
          C.[1] [C_1],
          C.[2] [C_2],
          C.[3] [C_3],
          [...]
  FROM
      AllCombinations 
      LEFT JOIN A ON AllCombinations.TechID = A.TechID
      LEFT JOIN B ON AllCombinations.TechID = B.TechID
      LEFT JOIN C ON AllCombinations.TechID = C.TechID
      [...]
  </code></pre>
  <p>I  feel this is by far not the best way to carry out the query. I´ve been trying out a lot of options and this is the most performant design I´ve come up with. Also, of course I´ve been using indexes, but it´s about the query design here.</p>
  <p>Is there a way to optimize this? In particular:</p>
  <ul>
  <li>The pivotised sections [A][B][C],... (in total up to 7) are nearly identical and just differ by the ClassId filter. Can I merge those queries somehow?</li>
  <li>Is a common table expression the best way to handle this?</li>
  </ul>
  <p><strong>UPDATE:</strong></p>
  <p>Here´s the complete, alienated query:</p>
  <pre><code>CREATE OR ALTER FUNCTION [dbo].[Complete_Pivot]
  (           
              @RDate DATETIME,
              @pid INT
  )
  RETURNS TABLE 
  AS
  RETURN 
  (
              WITH 
              PIDs AS
              (
                          SELECT PID, PPID  FROM PTree a WHERE a.RDate = @RDate AND a.PID = @pid AND a.PID NOT IN (9359,9360,9361)
                          UNION ALL
                          SELECT child.PID, child.PPID FROM PTree child INNER JOIN PIDs parent ON child.PPID = parent.PID WHERE child.RDate = @RDate AND child.PID NOT IN (9359,9360,9361)
              ),
              PreAggregated AS
              (
                          SELECT    
                                      se.[RDate]
                                      , rf.[RCID]
                                      , rf.[RMID]
                                      , de.[PID]
                                      , de.[PCID]
                                      , de.[DID]
                                      , de.INSID
                                      , de.INTID
                                      , de.[DDID]
                                      , de.[LID]
                                      , de.[LT]
                                      , de.[RC]                                                                      
                                      , SUM(se.[SVal]) as PreAggregatedValues                                           
                          FROM  [TableS] se
                                      INNER JOIN [TableRF] rf on rf.RFID = se.RFID
                                      INNER JOIN [TableD] de on de.DID = se.DID
                          WHERE se.RDate = @RDate AND de.PID IN (SELECT PID FROM PIDs)
                          GROUP BY 
                                      se.[RDate]
                                      , rf.[RCID]
                                      , rf.[RMID]
                                      , de.[PID]
                                      , de.[PCID]
                                      , de.[DID]
                                      , de.INSID
                                      , de.INTID
                                      , de.[DDID]
                                      , de.[LID]
                                      , de.[LT]
                                      , de.[RC]
              ),
              AllCombinations AS
              (
                          SELECT DID, [DDID], [PID],  [INSID], INTID, [LID],[LT],[RC],[RDate] FROM PreAggregated 
                          WHERE PID IN (SELECT PID FROM PIDs) AND RDate = @RDate
                          GROUP BY DID, [DDID],[PID],  [INSID], INTID, [LID],[LT],[RC],[RDate]
              ),
              [A] AS
              (
                          SELECT DID, [DDID],[PID],  [INSID], INTID, [LID],[LT],[RC],[RDate],SUM([1]) [1],SUM([2]) [2],SUM([3]) [3]
                          FROM (
                                      SELECT RMID, DID, [DDID],[PID],  [INSID], INTID, [LID],[LT],[RC],[RDate],[PreAggregatedValues] 
                                      FROM   PreAggregated
                                      WHERE RCID = 5 AND PID IN (SELECT PID FROM PIDs) AND RDate = @RDate) as DATA   
                                      PIVOT(
                                                 Sum(PreAggregatedValues)
                                                 FOR RMID IN ([1],[2],[3])) AS PIVOT
                          GROUP BY DID, [DDID],[PID],  [INSID], INTID, [LID],[LT],[RC],[RDate]
              ),
              [B] AS
              (
                          SELECT DID, [DDID],[PID],  [INSID], INTID, [LID],[LT],[RC],[RDate],SUM([1]) [1],SUM([2]) [2],SUM([3]) [3]
                          FROM (
                                      SELECT RMID, DID, [DDID],[PID],  [INSID], INTID, [LID],[LT],[RC],[RDate],[PreAggregatedValues] 
                                      FROM   PreAggregated
                                      WHERE RCID = 6 AND PID IN (SELECT PID FROM PIDs) AND RDate = @RDate) as DATA   
                                      PIVOT(
                                                 Sum(PreAggregatedValues)
                                                 FOR RMID IN ([1],[2],[3])) AS PIVOT
                          GROUP BY DID, [DDID],[PID],  [INSID], INTID, [LID],[LT],[RC],[RDate]
              ),
              [C] AS
              (
                          SELECT DID, [DDID],[PID],  [INSID], INTID, [LID],[LT],[RC],[RDate],SUM([1]) [1],SUM([2]) [2],SUM([3]) [3]
                          FROM (
                                      SELECT RMID, DID, [DDID],[PID],  [INSID], INTID, [LID],[LT],[RC],[RDate],[PreAggregatedValues] 
                                      FROM   PreAggregated
                                      WHERE RCID = 7 AND PID IN (SELECT PID FROM PIDs) AND RDate = @RDate) as DATA   
                                      PIVOT(
                                                 Sum(PreAggregatedValues)
                                                 FOR RMID IN ([1],[2],[3])) AS PIVOT
                          GROUP BY DID, [DDID],[PID],  [INSID], INTID, [LID],[LT],[RC],[RDate]
              ),
              [D] AS
              (
                          SELECT DID, [DDID],[PID],  [INSID], INTID, [LID],[LT],[RC],[RDate],SUM([1]) [1],SUM([2]) [2],SUM([3]) [3]
                          FROM (
                                      SELECT RMID, DID, [DDID],[PID],  [INSID], INTID, [LID],[LT],[RC],[RDate],[PreAggregatedValues] 
                                      FROM   PreAggregated
                                      WHERE RCID = 8 AND PID IN (SELECT PID FROM PIDs) AND RDate = @RDate) as DATA   
                                      PIVOT(
                                                 Sum(PreAggregatedValues)
                                                 FOR RMID IN ([1],[2],[3])) AS PIVOT
                          GROUP BY DID, [DDID],[PID],  [INSID], INTID, [LID],[LT],[RC],[RDate]
              ),
              [E] AS
              (
                          SELECT DID, [DDID],[PID],  [INSID], INTID, [LID],[LT],[RC],[RDate],SUM([1]) [1],SUM([2]) [2],SUM([3]) [3]
                          FROM (
                                      SELECT RMID, DID, [DDID],[PID],  [INSID], INTID, [LID],[LT],[RC],[RDate],[PreAggregatedValues] 
                                      FROM   PreAggregated
                                      WHERE RCID = 9 AND PID IN (SELECT PID FROM PIDs) AND RDate = @RDate) as DATA   
                                      PIVOT(
                                                 Sum(PreAggregatedValues)
                                                 FOR RMID IN ([1],[2],[3])) AS PIVOT
                          GROUP BY DID, [DDID],[PID],  [INSID], INTID, [LID],[LT],[RC],[RDate]
              )
  
              SELECT 
                          AllCombinations.[RDate],
                          AllCombinations.PID,
                          AllCombinations.[DID],
                          
                          AllCombinations.[INSID],
                          AllCombinations.INTID,
  
                          AllCombinations.[DDID],
  
                          AllCombinations.[LID],
                          AllCombinations.[LT],
                          AllCombinations.[RC],
                          
                          A.[1] [A_1],
                          A.[2] [A_2],
                          A.[3] [A_3],
                          B.[1] [B_1],
                          B.[2] [B_2],
                          B.[3] [B_3],
                          C.[1] [C_1],
                          C.[2] [C_2],
                          C.[3] [C_3],
                          D.[1] [D_1],
                          D.[2] [D_2],
                          D.[3] [D_3],
                          E.[1] [E_1],
                          E.[2] [E_2],
                          E.[3] [E_3],
              FROM
                          AllCombinations 
                          LEFT JOIN A ON AllCombinations.DID = A.DID
                          LEFT JOIN B ON AllCombinations.DID = B.DID
                          LEFT JOIN C ON AllCombinations.DID = C.DID
                          LEFT JOIN D ON AllCombinations.DID = D.DID
                          LEFT JOIN E ON AllCombinations.DID = E.DID
  )
  </code></pre>
  <p><strong>UPDATE 2:</strong></p>
  <p>Find the query plan here: <a href="https://www.brentozar.com/pastetheplan/?id=r1KBQOkgi" rel="nofollow noreferrer">https://www.brentozar.com/pastetheplan/?id=r1KBQOkgi</a></p>