Slow query when using database variables

Question

I use the following queries to retrieve 100 and 101 rows from the database and communication sessions after a time that is completely different (the second query is ~ 8 slower than the first):

SELECT TOP (100) * FROM PhotoLike WHERE photoAccountId=@accountId AND accountId<>@accountId ORDER BY createDate DESC GO 

SQL Server Runtime: CPU time = 187 ms, elapsed time = 202 ms.

 SELECT TOP (101) * FROM PhotoLike WHERE photoAccountId=@accountId AND accountId<>@accountId ORDER BY createDate DESC GO 

SQL Server Runtime: CPU time = 266 ms, elapsed time = 1644 ms.

Implementation plan for the first two cases:

But if I get rid of the @accoundId variable, I get the following results, which are approximately equal and more than 2 times faster than the first query from this question.

 SELECT TOP (100) * FROM PhotoLike WHERE photoAccountId=10 AND accountId<>10 ORDER BY createDate DESC GO 

SQL Server Runtime: CPU time = 358 ms, elapsed time = 90 ms.

 SELECT TOP (101) * FROM PhotoLike WHERE photoAccountId=10 AND accountId<>10 ORDER BY createDate DESC GO 

SQL Server Runtime: CPU time = 452 ms, elapsed time = 93 ms.

Implementation plan for the second two cases:

Why is this happening and how can I improve performance with varibales?

UPDATE

Implementation plans added.

Answer 1

There are a few things here.

When you use variables, SQL Server does not sniff values ​​at all, except if you also add OPTION (RECOMPILE) .

Estimating the number of lines matching photoAccountId=@accountId is much less with an assumption than it really is. (Note that the thick line coming out of the index is in the background and the decision to use a parallel plan).

Also, TOP 100 / TOP 101 is the cut-off point between TOP N sorting using an algorithm that just needs space to sort 100 rows, and it does a full sort .. Inaccurate row count accuracy probably means that there is not enough memory to completely sort, and it spills to tempdb .

Just adding OPTION (RECOMPILE) to a query with variables is likely to improve the situation, although it seems that even a “quick” plan performs many key searches that can be avoided with different indexing.

Answer 2

I wonder if this could be related to sniffing options. How fast is the next query done?

 DECLARE @accountIdParam int; SELECT @accountIdParam = @accountId; SELECT TOP (101) * FROM PhotoLike WHERE photoAccountId=@accountIdParam AND accountId<>@accountIdParam ORDER BY createDate DESC GO 

Answer 3

I can, you should create a clustered index based on the accountId field of your table.

When you test for inequality, it should be more efficient:

CREATE A UNIQUE CLUSTER INDEX [IX_MyIndexName] ON [dbo]. [PhotoLike] (accountId DESC, createDate DESC, photoAccountId DESC,)