Execute query command (dynamic sql) runs faster than ordinary query

Question

Why is the second query with EXECUTE command runs ~4 times faster than first query without one? How can I solve this problem?

Why additional table (Workatable) created in the second case?

Variables:

DECLARE @count INT, @followerId BIGINT
SET @count=1024
SET @followerId=10

First query (Usual query):

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

Log:

SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 7 ms.

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.

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.

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.

SQL Server Execution Times: CPU time = 1466 ms, elapsed time = 9620 ms.

Execution plan:

First query execution plan

Second query (the same query with EXECUTE):

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')

Log:

SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.

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.

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.

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.

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.

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.

SQL Server Execution Times: CPU time = 1374 ms, elapsed time = 2140 ms.

SQL Server Execution Times: CPU time = 1405 ms, elapsed time = 2165 ms.

Michael Goldshteyn Michael Goldshteyn · Accepted Answer · 2013-08-13T19:03:54

This could be because the second query (depending on the context of the first query) is more optimizable, since the variables become inline constants. Compare:

DECLARE @count INT, @followerId BIGINT
SET @count=1024
SET @followerId=10

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

With:

SELECT TOP (1024) Photo.* FROM Photo
WHERE  EXISTS (SELECT accountId FROM Follower
WHERE Follower.followerId=10
AND Follower.accountId = Photo.accountId) 
AND Photo.closed='False'
ORDER BY Photo.createDate DESC

This is especially true if your first query was part of a stored proc, the variables were arguments and that particular query was optimized with different parameter values when the stored proc was compiled.

Execute query command (dynamic sql) runs faster than ordinary query

1 Answers