title: Execute query command (dynamic sql) runs faster than ordinary query
body: <p>Why is the second query with <code>EXECUTE</code> command runs ~4 times faster than first query without one? How can I solve this problem?</p>
  
  <p>Why additional table (Workatable) created in the second case?</p>
  
  <p><strong>Variables:</strong></p>
  
  <pre><code>DECLARE @count INT, @followerId BIGINT
  SET @count=1024
  SET @followerId=10
  </code></pre>
  
  <p><strong>First query (Usual query):</strong></p>
  
  <pre><code>SELECT TOP (@count) Photo.* FROM Photo
  WHERE  EXISTS (SELECT accountId FROM Follower
  WHERE Follower.followerId=@followerId
  AND Follower.accountId = Photo.accountId) 
  AND Photo.closed='False'
  ORDER BY Photo.createDate DESC
  </code></pre>
  
  <p><strong>Log:</strong></p>
  
  <p>SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 7 ms.</p>
  
  <p>SQL Server Execution Times: CPU time = 0 ms,  elapsed time = 0 ms.</p>
  
  <p>Table 'Photo'. Scan count 952, logical reads 542435, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.</p>
  
  <p>Table 'Follower'. Scan count 1, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.</p>
  
  <p>SQL Server Execution Times: CPU time = 1466 ms,  elapsed time = <strong>9620 ms</strong>.</p>
  
  <p><strong>Execution plan:</strong></p>
  
  <p><img src="https://i.stack.imgur.com/z4itk.png" alt="First query execution plan"></p>
  
  <p><strong>Second query (the same query with <code>EXECUTE</code>):</strong></p>
  
  <pre><code>EXEC ('SELECT TOP (' +@count + ') Photo.* FROM Photo
  WHERE  EXISTS ( SELECT  accountId FROM Follower
  WHERE Follower.followerId=' +@followerId + '
  AND Follower.accountId = Photo.accountId) 
  AND Photo.closed=''False''
  ORDER BY Photo.createDate DESC')
  </code></pre>
  
  <p><strong>Log:</strong></p>
  
  <p>SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms.</p>
  
  <p>SQL Server Execution Times: CPU time = 0 ms,  elapsed time = 0 ms.</p>
  
  <p>SQL Server Execution Times: CPU time = 0 ms,  elapsed time = 0 ms.
  SQL Server parse and compile time:  CPU time = 25 ms, elapsed time = 25 ms.</p>
  
  <p>Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.</p>
  
  <p>Table 'Photo'. Scan count 952, logical reads 542707, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.</p>
  
  <p>Table 'Follower'. Scan count 6, logical reads 9, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.</p>
  
  <p>Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.</p>
  
  <p>SQL Server Execution Times: CPU time = 1374 ms,  elapsed time = 2140 ms.</p>
  
  <p>SQL Server Execution Times: CPU time = 1405 ms,  elapsed time = <strong>2165 ms</strong>.</p>