title: How to Optimize Query with Lots of Aggregates
body: <p>How do I optimize this query? Right now, it's running too slow ~10s. Full details below:</p>
  
  <pre><code>SELECT ProjectName, 
         Actuals_YTD, 
         Rem_Forecast, 
         Total_Forecast, 
         Approved_Budget, 
         Variance, 
         Variance_Percentage, 
         ProjectComments, 
         VersionType, 
         ModifiedDate 
  FROM (SELECT pd.ProjectId, 
               pd.ProjectName, 
               SUM(CASE WHEN RPD.PROJECTMONTH_TO_DATE(base.ProjectMonth) &lt;= '06/01/2011' THEN feb.USDactualamount ELSE 0.0 END) AS Actuals_YTD, 
               SUM(CASE WHEN RPD.PROJECTMONTH_TO_DATE(base.ProjectMonth) &gt; '06/01/2011' THEN feb.forecastusd ELSE 0.0 END) AS Rem_Forecast, 
               ((SUM(CASE WHEN RPD.PROJECTMONTH_TO_DATE(base.ProjectMonth) &lt;= '06/01/2011' THEN feb.USDactualamount ELSE 0.0 END)) + (SUM(CASE WHEN RPD.PROJECTMONTH_TO_DATE(base.ProjectMonth) &gt; '06/01/2011' then feb.forecastusd else 0.0 end))) AS Total_Forecast, 
               SUM(COALESCE((feb.REVISEDPLANUSD),0)) AS Approved_Budget, 
               ((SUM(CASE WHEN RPD.PROJECTMONTH_TO_DATE(base.ProjectMonth) &lt;= '06/01/2011' THEN feb.USDactualamount ELSE 0.0 END)) + (SUM(CASE WHEN RPD.PROJECTMONTH_TO_DATE(base.ProjectMonth) &gt; '06/01/2011' then feb.forecastusd else 0.0 end))) - ((SUM(COALESCE((feb.REVISEDPLANUSD),0)))) AS Variance, 
               CASE WHEN (SUM(COALESCE((feb.REVISEDPLANUSD),0))) = 0 THEN NULL ELSE ((((((SUM(CASE WHEN RPD.PROJECTMONTH_TO_DATE(base.ProjectMonth) &lt;= '06/01/2011' THEN feb.USDactualamount else 0.0 end)) + (SUM(CASE WHEN RPD.PROJECTMONTH_TO_DATE(projectmonth) &gt; '06/01/2011' then feb.forecastusd else 0.0 end)))) - (SUM(COALESCE((feb.REVISEDPLANUSD),0)))) / (SUM(COALESCE((feb.REVISEDPLANUSD),0)))) * 100) END AS Variance_Percentage, 
               pd.ProjectAux1, 
               pd.ProjectComments, 
               pd.VersionType, 
               MAX(base.ModifiedDate) AS ModifiedDate 
        FROM rpd.ProjectDetail pd  INNER JOIN rpd.FundSource fs ON pd.FundSourceId = fs.FundSourceId  
                                   INNER JOIN rpd.Baseline base ON pd.ProjectId = base.ProjectId  
                                   INNER JOIN rpd.FundEntityBaseline feb ON feb.BaselineId = base.BaselineId  
        GROUP BY pd.ProjectAux1, pd.ProjectId, pd.ProjectName, pd.ProjectComments, pd.VersionType)
  WHERE VersionType Like '%Text%' WITH UR
  </code></pre>
  
  <p>Here's the schema of 3 tables (FundSource is not included as it only has ~200 rows and I think it's pretty negligible)</p>
  
  <p>Schema:</p>
  
  <p><img src="https://i.stack.imgur.com/4u04g.png" alt="FundEntityBaseline">
  <img src="https://i.stack.imgur.com/4pkAN.png" alt="ProjectDetail">
  <img src="https://i.stack.imgur.com/pTuc4.png" alt="Baseline"></p>
  
  <p>Rows:</p>
  
  <ul>
  <li>FundEntityBaseline: 354603</li>
  <li>Baseline: 80208</li>
  <li>ProjectDetail: 1813</li>
  </ul>
  
  <p>Indices on ProjectDetail:</p>
  
  <ul>
  <li>1 Index for Primary Key (ProjectId)</li>
  <li>1 Index for Foreign Key (FundSourceId)</li>
  <li>1 Index for SELECT/GROUP BY containing columns (ProjectAux1,
  ProjectId, ProjectName, ProjectComments, VersionType)</li>
  <li>1 Index with (VersionType, ProjectName)</li>
  </ul>
  
  <p>Indices on Baseline:</p>
  
  <ul>
  <li>1 Index for Primary Key (BaselineId)</li>
  <li>1 Index for Foreign Key (ProjectId)</li>
  <li>1 Index with (ProjectTeamId, ProjectMonth)</li>
  <li>1 Index with ProjectMonth only</li>
  </ul>
  
  <p>Indices on FundEntityBaseline</p>
  
  <ul>
  <li>1 Index for Primary Key (FundEntityBaselineId)</li>
  <li>1 Index for Foreign Key (BaselineId)</li>
  </ul>
  
  <p>Latest access plan:
  <img src="https://i.stack.imgur.com/1qcKN.png" alt="Access Plan"></p>