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

#sql #sql-server #datetime #variables

#sql #sql-server #дата и время #переменные

Вопрос:

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

 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. Попробуйте выполнить запросы несколько раз, чтобы проверить, соответствуют ли временные интервалы. Вероятно, оба должны иметь одинаковую производительность.

2. конечно, метод 2 занимает больше времени по сравнению с методом 1, потому что каждый раз при запросе данных ему необходимо ссылаться на этот «set @var1=’10/9/16 00:00:00:000′ » данные

3. Проверьте планы запросов. Возможно, в кэше есть плохой план для второго запроса.

Ответ №1:

Это называется перехватом параметров

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

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

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

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

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

Недостатки

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

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

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

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

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

Обнюхивание параметров

Ответ №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)

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

1. Сообщение 156, уровень 15, состояние 1. Неправильный синтаксис рядом с ключевым словом ‘option’. (Строка 7)

2. объявить @var1 установить дату и время @var1=’10/9/16 00:00:00:000′ выберите верхний (1) ciid, businessdate из checkitemsales, где (businessdate = @var1) опция (перекомпиляция) привела к менее чем за секунду!

Ответ №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)