title: Why Does Select Top X Kill Performance
body: <p>I have the T-SQL Query below (generated by linq-to-sql, so it looks a bit weird.)  When I <em>remove</em> the top 10, it runs in under 1 second.  When I leave the top 10 in, it takes 68 seconds.  </p>
  
  <p>Without the top 10, the total number of rows is only 19.</p>
  
  <p>Why does the top 10 destroy performance so much in this situation?</p>
  
  <pre><code>SELECT TOP (10) [t3].[value] AS [StartDate], [t3].[AH_TimeStamp4] AS [EndDate], [t3].[value2] AS [RunTime], [t3].[AH_Idnr] AS [RunNumber], [t3].[AH_Status] AS [StatusCode], [t3].[STATUS_DESC] AS [StatusDesc], [t3].[STATUS_TYPE] AS [StatusType]
  FROM (
      SELECT COALESCE([t1].[AH_TimeStamp2],[t1].[AH_TimeStamp1]) AS [value], [t1].[AH_TimeStamp4], CONVERT(BigInt,(((CONVERT(BigInt,DATEDIFF(DAY, [t1].[AH_TimeStamp1], [t1].[AH_TimeStamp4]))) * 86400000) + DATEDIFF(MILLISECOND, DATEADD(DAY, DATEDIFF(DAY, [t1].[AH_TimeStamp1], [t1].[AH_TimeStamp4]), [t1].[AH_TimeStamp1]), [t1].[AH_TimeStamp4])) * 10000) AS [value2], [t1].[AH_Idnr], [t1].[AH_Status], [t2].[STATUS_DESC], [t2].[STATUS_TYPE], [t0].[OH_Name]
      FROM [dbo].[OH] AS [t0]
      INNER JOIN [dbo].[AH] AS [t1] ON ([t0].[OH_Idnr]) = [t1].[AH_OH_Idnr]
      INNER JOIN [dbo].[CHK_JOB_STATUS_CODE] AS [t2] ON [t1].[AH_Status] = ([t2].[STATUS_CODE])
      ) AS [t3]
  WHERE [t3].[OH_Name] = @p0
  ORDER BY [t3].[AH_Idnr] DESC
  </code></pre>
  
  <p>Edit ->  as requested, here's the linq-generated query without the Take(10)</p>
  
  <pre><code>SELECT [t3].[value] AS [StartDate], [t3].[AH_TimeStamp4] AS [EndDate], [t3].[value2] AS [RunTime], [t3].[AH_Idnr] AS [RunNumber], [t3].[AH_Status] AS [StatusCode], [t3].[STATUS_DESC] AS [StatusDesc], [t3].[STATUS_TYPE] AS [StatusType]
  FROM (
      SELECT COALESCE([t1].[AH_TimeStamp2],[t1].[AH_TimeStamp1]) AS [value], [t1].[AH_TimeStamp4], CONVERT(BigInt,(((CONVERT(BigInt,DATEDIFF(DAY, [t1].[AH_TimeStamp1], [t1].[AH_TimeStamp4]))) * 86400000) + DATEDIFF(MILLISECOND, DATEADD(DAY, DATEDIFF(DAY, [t1].[AH_TimeStamp1], [t1].[AH_TimeStamp4]), [t1].[AH_TimeStamp1]), [t1].[AH_TimeStamp4])) * 10000) AS [value2], [t1].[AH_Idnr], [t1].[AH_Status], [t2].[STATUS_DESC], [t2].[STATUS_TYPE], [t0].[OH_Name]
      FROM [dbo].[OH] AS [t0]
      INNER JOIN [dbo].[AH] AS [t1] ON ([t0].[OH_Idnr]) = [t1].[AH_OH_Idnr]
      INNER JOIN [dbo].[CHK_JOB_STATUS_CODE] AS [t2] ON [t1].[AH_Status] = ([t2].[STATUS_CODE])
      ) AS [t3]
  WHERE [t3].[OH_Name] = @p0
  ORDER BY [t3].[AH_Idnr] DESC
  </code></pre>
  
  <p>Edit 2 -> here's the LINQ Query - nothing special, just 3 joins</p>
  
  <pre><code>List&lt;UC4Status&gt; statusList = uc4DB.OHs.Where(o =&gt; o.OH_Name == jobName).Join(uc4DB.AHs, oh =&gt; oh.OH_Idnr, ah =&gt; ah.AH_OH_Idnr, (oh, ah) =&gt; ah)
                          .Join(uc4DB.CHK_JOB_STATUS_CODEs, ah =&gt; ah.AH_Status, job =&gt; job.STATUS_CODE, (a, s) =&gt;
                              new UC4Status
                              {
                                  StartDate = a.AH_TimeStamp2 ?? a.AH_TimeStamp1,
                                  EndDate = a.AH_TimeStamp4,
                                  RunTime = a.AH_TimeStamp4 - a.AH_TimeStamp1,
                                  StatusType = s.STATUS_TYPE,
                                  StatusDesc = s.STATUS_DESC,
                                  StatusCode = a.AH_Status,
                                  RunNumber = a.AH_Idnr
                              }).OrderByDescending(r =&gt; r.RunNumber).Take(maxResults).ToList();
  </code></pre>