Следует ли избегать локальных переменных для констант в хранимых процедурах?

Когда я пишу SQL, я стараюсь сделать его максимально читаемым. Среди прочего я часто объявляю "константы" вместо использования "магических чисел".

то есть. вместо

WHERE [Order].OrderType = 3

Я делаю

DECLARE @OrderType_Cash AS int = 3;
...
WHERE [Order].OrderType = @OrderType_Cash

Это отлично работает, и я не заметил никаких проблем с производительностью для размера запросов и данных, с которыми я обычно работаю.

Недавно я прочитал статью о параметрах sniffing и обходных решениях (https://blogs.msdn.microsoft.com/turgays/2013/09/10/parameter-sniffing-problem-and-possible-workarounds/). В искусстве один из обходных решений представлен "использовать локальные переменные".

Обходной путь: используйте локальную переменную. Это обходное решение очень похоже на предыдущее (OPTION (OPTIMIZE FOR (@VARIABLE UNKNOWN))) - когда вы присваиваете параметрам локальные SQL Server использует статистику плотности вместо статистических гистограмм - So Он оценивает то же самое количество записей для всех параметров. Недостатком является то, что некоторые запросы будут использовать субоптимальные планы, поскольку плотности не точны достаточно как статистическая гистограмма.

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

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

Может кто-нибудь прояснит это для меня?

Есть ли разница между использованием "магического числа" и локальной переменной?
Если да, то это только в хранимых процедурах или применяется также к ad-hoc-запросам и динамическому SQL?
Это плохая привычка использовать локальные переменные, как я?

Ответ 1

Как описано в статье Статистика, используемая Оптимизатором запросов в Microsoft SQL Server 2005

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

Относительно ваших вопросов...

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

Нет, никогда, он не может авто параметризовать adhoc-запросы, но параметры ведут себя иначе, чем переменные, и их можно обнюхивать. По умолчанию он будет делать это только в очень ограниченных условиях, когда он "безопасен" и вряд ли приведет к проблемам с параметрами нюхания.

Есть ли разница между использованием "магического номера" и локального переменная?

Да, оператор обычно компилируется до того, как значение переменной даже присвоено. И даже если утверждение должно было быть подвергнуто отсроченной компиляции (или, быть может, перекомпилировано после присваивания), значения переменных по-прежнему никогда не нюхаются, за исключением случаев, когда вы используете option (recompile). Если вы используете литеральный встроенный SQL Server, вы можете найти это буквальное значение в гистограмме и, возможно, получить гораздо более точные оценки, а не прибегать к догадкам. Точные оценки строк важны для получения правильной общей формы плана (например, тип соединения типа или метода доступа), а также получения соответствующего разрешения на выделение для вашего запроса.

В книге "Практическое устранение неполадок SQL Server 2005" говорится об этом.

В SQL Server 2005 компиляция уровня инструкций позволяет компилировать отдельного заявления в хранимой процедуре, подлежащей отсрочке до непосредственно перед первым выполнением запроса. К тому времени локальный значение переменной будет известно. Теоретически SQL Server может Преимущество этого в том, чтобы обнюхать локальные значения переменных так же, как он обнюхивает параметры. Однако, поскольку было распространено использование локальных переменные, чтобы победить параметр sniffing в SQL Server 7.0 и SQL Сервер 2000+, sniffing локальных переменных не был включен в SQL Server 2005. Он может быть включен в будущем выпуске SQL Server, хотя

(NB: на самом деле это не было включено в любой версии до настоящего времени)

Если да, это только в хранимых процедурах, или это также применяются к специальным запросам и динамическому sql?

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

Является ли плохой привычкой использовать локальные переменные, как я?

Если план будет чувствителен к точному значению переменной, чем yes. Есть определенные места, где это будет совершенно безобидно.

Недостатком option (recompile) как исправления является то, что он перекомпилирует оператор каждый раз. Это необязательно, когда единственная причина для этого - заставить его обнюхать переменную, значение которой постоянно. Недостатком option (optimize for) с конкретным литеральным значением является то, что при изменении значения вам нужно также обновить все эти ссылки.

Другим подходом было бы создание представления констант.

CREATE VIEW MyConstants
AS
SELECT 3 AS OrderTypeCash, 4 AS OrderTypeCard

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

WHERE [Order].OrderType = (SELECT OrderTypeCash FROM MyConstants)

Это позволит разрешить значение во время компиляции и обновляться только в одном месте.

В качестве альтернативы, если вы используете проекты SSDT и базы данных, вы можете использовать переменную sqlcmd, которая определяется один раз и назначается, а затем заменяет все ссылки на эту переменную TSQL. Код, развернутый на сервере, по-прежнему будет иметь "магические числа", но в вашем исходном коде это единственная переменная SqlCmd (NB: для этого шаблона вам может потребоваться создать процедуру заглушки в проекте и использовать пост-развертывание script для на самом деле изменить его с помощью требуемого определения и выполнить подстановки sqlcmd).