SQL Variable takes longer to return than static value

Question

I have a table with two values:

ciid, businessdate

ciid is the primary key, and has auto increment turned on. businessdate (datetime) is inserted by another process.

given the following queries:

select top(1) ciid, businessdate
from checkitemsales 
where businessdate='10/9/16 00:00:00:000'

This only takes 1.2 seconds to return, whereas this query:

declare @var1 datetime

set @var1='10/9/16 00:00:00:000'

select top(1) ciid, businessdate
from checkitemsales
where businessdate = @var1

takes over 5.6 seconds to return.

can anyone tell me what I'm doing wrong?

Try running the queries multiple times to see if the timings are consistent. The two should probably have the same performance. — Gordon Linoff, Oct 11 '16 at 01:11
of course method 2 take longer time comparing to method 1,because everytime when query the data,it need refer to this "set @var1='10/9/16 00:00:00:000' " data — KyLim, Oct 11 '16 at 01:14
Check the query plans. Maybe there's a bad plan in the cache for the second query. — Blorgbeard, Oct 11 '16 at 02:03

Pரதீப் · Answer 1 · 2016-10-11T02:47:29.757

This is called Parameter sniffing

when executing queries or stored procedures that use parameters. During compilation, the value passed into the parameter is evaluated and used to create an execution plan. That value is also stored with the execution plan in the plan cache. Future executions of the plan will re-use the plan that was compiled with that reference value.

You can avoid this by various methods. one is

Recompiling

You can add the option(Recompile) to the query so that every time the query is compiled a new execution plan will be generated

select top(1) ciid, businessdate
from checkitemsales
where businessdate = @var1
OPTION (RECOMPILE);

Disadvantages

Queries run frequently.
CPU resources are limited.
Some variance in query performance is acceptable.

Other methods are

Optimize For Value
Optimize For Unknown
Exceptions

Check the below articles on details of all the above methods

sp_BlitzCache™ Result: Parameter Sniffing

Parameter Sniffing

KyLim · Answer 2 · 2016-10-11T01:59:18.427

1

declare @var1 datetime

set @var1='10/9/16 00:00:00:000'

select top(1) ciid, businessdate
from checkitemsales
where (businessdate = @var1) option (recompile)

try this,and let me know the result,it might be faster

edited Oct 11 '16 at 01:59

answered Oct 11 '16 at 01:18

KyLim

468
1
6
22

Msg 156, Level 15, State 1. Incorrect syntax near the keyword 'option'. (Line 7) – kloodge Oct 11 '16 at 01:57
declare @var1 datetime set @var1='10/9/16 00:00:00:000' select top(1) ciid, businessdate from checkitemsales where (businessdate = @var1) option (recompile) resulted in less than a second! – kloodge Oct 11 '16 at 01:59

score 0 · Answer 3 · answered Oct 11 '16 at 05:07

Can you try this approach:

declare @var1 datetime
set @var1='10/9/16 00:00:00:000'

declare @cmd varchar(max) = 'select top(1) ciid, businessdate
from #table
where businessdate = ''' + CONVERT(VARCHAR(10), @var1, 1) + ' '  + convert(VARCHAR(12), @var1, 114) + ''''

EXEC (@cmd)

SQL Variable takes longer to return than static value

3 Answers3