SQL-переменная занимает больше времени, чем статическое значение

У меня есть таблица с двумя значениями:

ciid, businessdate

ciid является первичным ключом и включается автоматически. businessdate (datetime) вставляется другим процессом.

заданы следующие запросы:

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

Это займет всего 1,2 секунды, тогда как этот запрос:

declare @var1 datetime

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

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

требуется 5,6 секунды для возврата.

может ли кто-нибудь сказать мне, что я делаю неправильно?

Ответ 1

Это называется Параметр sniffing

при выполнении запросов или хранимых процедур, использующих параметры. Во время компиляции значение, переданное в параметр, оценивается и используется для создания плана выполнения. Это значение также сохраняется с планом выполнения в кеше плана. Будущие исполнения плана будут повторно использовать план, который был скомпилирован с этим эталонным значением.

Вы можете избежать этого различными способами. один

Перекомпиляция

Вы можете добавить option(Recompile) в запрос, чтобы каждый раз, когда запрос был скомпилирован, будет создан новый план выполнения

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

Недостатки

Запросы выполняются часто.
Ресурсы процессора ограничены.
Возможна некоторая разница в производительности запросов.

Другие методы

Оптимизировать значение
Оптимизация для неизвестных
Исключения

Ознакомьтесь с приведенными ниже статьями о всех приведенных выше методах.

sp_BlitzCache ™ Результат: Sniffing параметров

Параметр 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)

попробуйте это, и дайте мне знать результат, он может быть быстрее

Ответ 3

Можете ли вы попробовать этот подход:

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)