CODEWITHSUNDEEP
sqlsql-serverdatabase-performance
Ivan Kochurkin
Ivan Kochurkin

Reputation: 4491

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

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.

Upvotes: 2

Views: 2140

Answers (1)

Michael Goldshteyn
Michael Goldshteyn

Reputation: 74410

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.

Upvotes: 4

Related Questions