SQL变量返回的时间比静态值长

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.

ciid是主键，并启用自动增量。businessdate (datetime)由另一个进程插入。

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:

这只需要1.2秒返回，而这个查询:

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.

返回时间超过5.6秒。

can anyone tell me what I'm doing wrong?

有人能告诉我我做错了什么吗?

3 个解决方案

#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.
CPU资源是有限的。
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

sp_BlitzCache™结果:参数嗅探

Parameter Sniffing

参数嗅探

#2

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

试试这个，让我知道结果，可能会更快

#3

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)

#1