Low performance query while using database variables

Question

I using next queries for extracting top 100 and 101 lines from DB and gettings following elapsing times, which completely different (second query ~8 slower than first):

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

SQL Server Execution Times: 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 Execution Times: CPU time = 266 ms, elapsed time = 1644 ms.

Execution plan of first two cases: Select top 100 and 101 with variable

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

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

SQL Server Execution Times: 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 Execution Times: CPU time = 452 ms, elapsed time = 93 ms.

Execution plan of second two cases: Select top 100 and 101 without variable

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

UPDATE

Added execution plans.

TOP 100/TOP 101 is known problem, see this blog. Speed degradation with parameters is probably due to parameter sniffing. — Nikola Markovinović
– Nikola Markovinović, Commented Aug 8, 2013 at 12:48
@NikolaMarkovinović - This is the opposite of parameter sniffing, Variables aren't sniffed except if using OPTION (RECOMPILE) so it just makes a generic guess not based on the actual values at all. The TOP 101 behaviour isn't always a problem though looks like it is in this case. It is the cut off point between doing a full sort and a TOP N sort. — Martin Smith
– Martin Smith, Commented Aug 8, 2013 at 12:59
@MarkSowul - Nope. In fact assigning to local variables is still a semi popular workaround to avoid parameter sniffing precisely because they aren't sniffed. — Martin Smith
– Martin Smith, Commented Aug 8, 2013 at 13:04
@MartinSmith Thanks for the info. I assumed OP is using stored procedure - that would explain timings. As for TOP 100 i never liked solution given in this blog, and after browsing through your link I know why :-) — Nikola Markovinović
– Nikola Markovinović, Commented Aug 8, 2013 at 13:06

Community · Accepted Answer · 2018-11-13 23:29:15Z

2

There are a couple of things going on here.

When you use variables SQL Server doesn't sniff the values at all except if you also add OPTION (RECOMPILE).

The estimate for the number of rows matching photoAccountId=@accountId is much smaller with the guess than is actually the case. (Note the thick line coming out of the index seek in the second plan and the decision to use a parallel plan).

Also TOP 100 / TOP 101 is the cut off point between the TOP N sort using an algorithm that just needs space to sort 100 rows and it doing a full sort.. The inaccurate row count estimate likely means there is insufficient memory allocated for the full sort and it is spilling to tempdb.

Simply adding OPTION (RECOMPILE) to the query with variables will likely improve things somewhat though it looks as though even the "fast" plan is doing many key lookups that could be avoided with different indexing.

edited Nov 13, 2018 at 23:29

CommunityBot

11 silver badge

answered Aug 8, 2013 at 13:19

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sam · Accepted Answer · 2013-08-08 12:53:53Z

0

I wonder if this could be parameter sniffing related. How fast does the following query go?

DECLARE @accountIdParam int;
SELECT @accountIdParam = @accountId;

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

answered Aug 8, 2013 at 12:53

Sam

1,37616 silver badges29 bronze badges

1 Comment

Sam Over a year ago

@KvanTTT Well, what I gave is a work-around for parameter sniffing, so it doesn't seem like that's the problem.

Rom Eh · Accepted Answer · 2013-08-08 15:35:33Z

0

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

As you test a inequality, it should be more performant :

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

answered Aug 8, 2013 at 15:35

Rom Eh

2,0711 gold badge17 silver badges34 bronze badges

Collectives™ on Stack Overflow

Low performance query while using database variables

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related