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?

Answer 1

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