title: Need to Improve Performance of SQL Query using Aggregate Functions
body: <p>I have a particular SQL query that seems to suffer from a mysterious performance issue. Here is the query:</p>
  
  <pre><code>SELECT COUNT(LengthOfTime) AS TotalTime, 
         SUM(LengthOfTime) AS TotalLength, 
         SUM(LengthOfTime) / COUNT(LengthOfTime) AS AverageTime, 
         SUM(Pops) / COUNT(LengthOfTime) AS AveragePop 
    FROM ((SELECT * 
             FROM (SELECT *, ID &amp; YearRec AS ID2 
                     FROM MyFirstTable 
                   UNION ALL 
                   SELECT *, ID &amp; YearRec AS ID2 
                     FROM Table2011) AS TEMP 
            WHERE STARTTIME &gt;= '8/1/2011 00:00:00' 
              AND StartTime &lt;= '8/5/2011 23:59:59' ) AS TEMP2 
    JOIN AppleTable ON TEMP2.Reason = AppleTable.Skills ) 
    JOIN PeopleTable ON TEMP2.Operator = PeopleTable.Operators 
   WHERE AppleTable.[ON] = 1 
     AND PeopleTable.[ON] = 1 
     AND Rec_Type = 'SECRET AGENT'
  </code></pre>
  
  <p>The issue here is that this query runs very quickly (0:00 to 0:02) when run for a 5 day span, but very slowly (1:20 to 1:45) for a 6 day span. </p>
  
  <p>There are approximately 105,000 records per day in the Tables (MyFirstTable and Table2011). </p>
  
  <p><em><strong>My question</em></strong>: Is there an upper limit to the number of rows you can pass an aggregate function before you see a serious performance issue in SQL Server? (currently using 2008 R2)</p>