Запрос выполняется медленно при использовании параметров, но быстро при использовании литерала

У меня есть запрос, который работает с довольно большой таблицей, мне нужно подсчитать его.

Если я использую литерал, запрос выполняется за несколько секунд, но когда я помещаю значения в качестве переменных (что мне нужно сделать), запрос занимает вечность и, по-видимому, выполняет полное сканирование таблицы.

Я довольно много читал об этом, и я понимаю, что это, скорее всего, связано с обнюхиванием параметров, что я не могу притворяться, что понимаю это, я просто хочу знать, как я могу это исправить, иначе я придется вернуться к вызову его в С# со сгенерированными строками запроса.

Этот запрос выполняется через несколько секунд.

  SELECT Count(Id) FROM  dbo.BeaconScan WHERE State = 'Archived' AND  LastSeen < '29 February 2020';

Это занимает вечность

DECLARE @Date DATE = '31 March 2020'
DECLARE @Status NVARCHAR(256) = 'Archived'
SELECT Count(Id) FROM  dbo.BeaconScan WHERE State = @Status AND  LastSeen < @Date;

sql-server azure-sql-database parameter-sniffing
person NoseBagUK    schedule 06.11.2020    source источник
comment
Вы используете хранимую процедуру или нет?   -  person Dusan    schedule 06.11.2020
comment
Действительно ли параметризованная версия находится внутри процедуры? Если да, то являются ли эти параметры локальными переменными или параметрами процедуры?   -  person Arvo    schedule 06.11.2020
comment
Вы читали stackoverflow.com/questions/4459425/?   -  person Nikita Chayka    schedule 06.11.2020
comment
Мы столкнулись с той же проблемой на APS. Насколько я правильно помню, нам нужно было добавить в запрос что-то вроде оптимизированного для неизвестного или что-то в этом роде - не знаю, работает ли это и на Azure.   -  person Tyron78    schedule 06.11.2020

Ответы (4)


arrow_upward
1
arrow_downward

Если вы используете хранимую процедуру, чтобы исключить прослушивание параметров, сделайте следующее:

DECLARE @DateLocal DATE = @Date
DECLARE @StatusLocal NVARCHAR(256) = @Status

SELECT Count(Id) FROM dbo.BeaconScan WHERE State = @StatusLocal AND LastSeen < @DateLocal
person Dusan    schedule 06.11.2020

arrow_upward
1
arrow_downward

Вы должны проверить свой actual plans, чтобы убедиться, что проблема в анализе параметров. Только фактический план показывает фактическое количество строк по сравнению с ожидаемым + параметр, для которого был построен план.

Если вы хотите, чтобы ваш запрос использовал ваш фактический параметр каждый раз, вы можете добавить опцию recompile на уровне запроса:

SELECT Count(Id) FROM  dbo.BeaconScan WHERE State = @Status AND  LastSeen < @Date
option(recompile);
person sepupic    schedule 06.11.2020

arrow_upward
1
arrow_downward

SQL Server оптимизирует запросы на основе оценок количества строк и эвристики. Эти оценки могут отличаться литералами, локальными переменными или параметрами.

С литералом или параметром (параметр, объявленный в коде приложения и переданный с помощью команды), SQL Server оценивает количество на основе предоставленного фактического значения и гистограммы статистики (если для столбца существует индекс или статистика). Как правило, это приводит к точным оценкам и оптимальному плану, когда статистика актуальна.

С локальной переменной (оператор T-SQL DECLARE) или подсказкой запроса OPTIMIZE FOR UNKNOWN SQL Server оценивает количество на основе общей средней плотности значений и игнорирует фактическое значение и гистограмму. Как правило, это приводит к компромиссному плану, который может быть достаточно хорошим в целом, но может быть неоптимальным для определенных значений. Добавление подсказки запроса OPTION (RECOMPILE) к запросу с локальными переменными вместо этого будет использовать фактические значения локальных переменных для оптимизации и даст тот же план, как если бы были указаны литералы.

Обратите внимание, что параметризованные планы запросов без подсказки RECOMPILE кэшируются и используются повторно. Текущие значения параметров игнорируются при повторном использовании плана, поэтому повторно используемый план запроса может быть неоптимальным для текущих значений параметров. Это еще один случай, когда OPTION (RECOMPILE) может повысить производительность.

Используйте подсказку OPTION (RECOMPILE) разумно, учитывая частоту выполнения запросов. Накладные расходы на компиляцию могут перевесить экономию времени выполнения для запросов, которые выполняются часто (например, много раз в секунду).

person Dan Guzman    schedule 06.11.2020

arrow_upward
1
arrow_downward

С буквальной датой оптимизатор может определить, будет ли SEEK выполнять SCAN. Если в таблице есть данные за много лет, но запрос запрашивает данные только после 29 февраля 2020 года, оптимизатор может определить, что ему нужен небольшой набор данных, и выполнит поиск. Запрос будет выполняться относительно быстро.

Оптимизатор рассматривает переменную дату как неизвестную. Следовательно, оптимизатор должен составить план, учитывающий такие даты, как 1 января 2001 г. или 12 декабря 2012 г. Большие наборы данных лучше подходят для SCAN (сканирование индекса или сканирование таблицы). Учитывая неизвестное значение, оптимизатор часто выбирает SCAN. Запрос будет выполняться намного дольше, потому что он считывает каждую строку и не использует индексы.

Чтобы избежать неизвестного, вы можете использовать подсказку запроса OPTIMIZE FOR. Но, в зависимости от вашего варианта использования, это может ничем не отличаться от использования литерала.

Анализ параметров обычно относится к хранимым процедурам. Чтобы этого избежать, назначьте параметр процедуры переменной в первой или двух строках процедуры. Нет необходимости знать полное объяснение перехвата параметров, чтобы избежать этого.

person jim    schedule 08.11.2020