title: Why would using a temp table be faster than a nested query?
body: <p>We are trying to optimize some of our queries. </p>
  
  <p>One query is doing the following:</p>
  
  <pre><code>SELECT t.TaskID, t.Name as Task, '' as Tracker, t.ClientID, (&lt;complex subquery&gt;) Date,
  INTO [#Gadget]
  FROM task t
  
  SELECT TOP 500 TaskID, Task, Tracker, ClientID, dbo.GetClientDisplayName(ClientID) as Client 
  FROM [#Gadget]
  order by CASE WHEN Date IS NULL THEN 1 ELSE 0 END , Date ASC
  
  DROP TABLE [#Gadget]
  </code></pre>
  
  <p>(I have removed the complex subquery. I don't think it's relevant other than to explain why this query has been done as a two stage process.)</p>
  
  <p>I <em>thought</em> it would be far more efficient to merge this down into a single query using subqueries as:</p>
  
  <pre><code>SELECT TOP 500 TaskID, Task, Tracker, ClientID, dbo.GetClientDisplayName(ClientID)
  FROM
  (
      SELECT t.TaskID, t.Name as Task, '' as Tracker, t.ClientID, (&lt;complex subquery&gt;) Date,
      FROM task t
  ) as sub    
  order by CASE WHEN Date IS NULL THEN 1 ELSE 0 END , Date ASC
  </code></pre>
  
  <p>This would give the optimizer better information to work out what was going on and avoid any temporary tables. I assumed it should be faster.</p>
  
  <p>But it turns out it is a lot slower. 8 seconds vs. under 5 seconds.</p>
  
  <p>I can't work out why this would be the case, as all my knowledge of databases imply that subqueries would always be faster than using temporary tables.</p>
  
  <p>What am I missing?</p>
  
  <p><strong>Edit</strong> --</p>
  
  <p>From what I have been able to see from the query plans, both are largely identical, except for the temporary table which has an extra "Table Insert" operation with a cost of 18%. </p>
  
  <p>Obviously as it has two queries the cost of the Sort Top N is a lot higher in the second query than the cost of the Sort in the Subquery method, so it is difficult to make a direct comparison of the costs.</p>
  
  <p>Everything I can see from the plans would indicate that the subquery method would be faster.</p>