Лучший шаблон для Констант в SQL?
Я видел, что несколько шаблонов раньше 'преодолевали' отсутствие констант в SQL Server, но ни один из них, кажется, не удовлетворяет и производительность и удобочитаемость / проблемы пригодности для обслуживания.
В ниже примера, предполагая, что у нас есть интегральная классификация 'состояния' на нашей таблице, опции, кажется:
- Только трудно кодировать его и возможно просто 'прокомментировать' состояние
-- StatusId 87 = Loaded
SELECT ... FROM [Table] WHERE StatusId = 87;
- Используя справочную таблицу для состояний, и затем соединяющий с этой таблицей так, чтобы
WHEREпункт ссылается на дружественное имя.
SubQuery:
SELECT ...
FROM [Table]
WHERE
StatusId = (SELECT StatusId FROM TableStatus WHERE StatusName = 'Loaded');
или присоединенный
SELECT ...
FROM [Table] t INNER JOIN TableStatus ts On t.StatusId = ts.StatusId
WHERE ts.StatusName = 'Loaded';
- Набор скалярного UDF's, определенного, которые возвращают константы, то есть
CREATE Function LoadedStatus()
RETURNS INT
AS
BEGIN
RETURN 87
END;
и затем
SELECT ... FROM [Table] WHERE StatusId = LoadedStatus();
(IMO, это вызывает большое загрязнение в базе данных - это могло бы быть в порядке в обертке пакета Oracle),
- И подобные шаблоны с Табличными функциями, содержащими константы со значениями как строки или столбцы, которые являются
CROSS APPLIEDназад к[Table]
Как другой имеют, ТАКИМ ОБРАЗОМ, пользователи решили эту распространенную проблему?
Редактирование: Щедрость - у кого-либо есть метод лучшей практики для поддержания $ (переменные) в DDL DBProj / Сценарии схемы согласно ответу Remus и комментарию?
4 ответа
Жесткое кодирование. В SQL производительность превосходит ремонтопригодность.
Последствия в плане выполнения между использованием константы, которую оптимизатор может проверить во время генерации плана, и использованием любой формы косвенности (UDF, JOIN, подзапрос) часто драматичны. Компиляция SQL - это необычный процесс (в том смысле, что не "обычный", как, скажем, генерация IL-кода), поскольку результат определяется не только компилируемой языковой конструкцией (т.е. фактическим текстом запроса), но и схемой данных (существующими индексами) и фактическими данными в этих индексах (статистикой). Когда используется жестко заданное значение, оптимизатор может выдать лучший план, поскольку он может фактически проверить значение по статистике индекса и получить оценку результата.
Еще одно соображение заключается в том, что SQL-приложение - это не только код, но в значительной степени код и данные. Рефакторинг" SQL-программы - это... другое дело. Если в программе на C# можно изменить константу или перечисление, перекомпилировать и счастливо запустить приложение, то в SQL это невозможно, потому что значение, скорее всего, присутствует в миллионах записей в базе данных, и изменение значения константы подразумевает изменение гигабайтов данных, часто онлайн, пока происходят новые операции.
То, что значение жестко закодировано в запросах и процедурах, которые видит сервер, не обязательно означает, что оно должно быть жестко закодировано в исходном коде проекта. Существуют различные инструменты генерации кода, которые могут позаботиться об этом. Рассмотрим такой тривиальный вариант, как использование переменных сценария sqlcmd:
defines.sql:
:setvar STATUS_LOADED 87
somesource.sql:
:r defines.sql
SELECT ... FROM [Table] WHERE StatusId = $(STATUS_LOADED);
someothersource.sql:
:r defines.sql
UPDATE [Table] SET StatusId = $(STATUS_LOADED) WHERE ...;
Я использовал опцию скалярной функции в нашей БД, и она работает нормально, и, по моему мнению, это лучший способ этого решения.
если к одному элементу относятся несколько значений, выполняется поиск, как если бы вы загружали поле со списком или любой другой элемент управления со статическим значением, тогда используйте поиск, который является лучшим способом сделать это.
Хотя я согласен с Ремусом Русану, ИМО, ремонтопригодность кода (и, следовательно, удобочитаемость, наименьшее удивление и т. Д.) Превосходит другие проблемы, если разница в производительности не является достаточно значительной, чтобы гарантировать обратное. Таким образом, следующий запрос теряет удобочитаемость:
Select ..
From Table
Where StatusId = 87
В общем, когда у меня есть зависящие от системы значения, на которые будут ссылаться в коде (возможно, имитированные в перечислении по имени), я использую строковые первичные ключи для таблиц, в которых они хранятся . Сравните это с изменяемыми пользователем данными, в которых я обычно использую суррогатные ключи.Использование первичного ключа, требующего ввода, помогает (хотя и не идеально) указать другим разработчикам, что это значение не должно быть произвольным.
Таким образом, моя таблица «Статус» будет выглядеть так:
Create Table Status
(
Code varchar(6) Not Null Primary Key
, ...
)
Select ...
From Table
Where StatusCode = 'Loaded'
Это делает запрос более читабельным, не требует соединения с таблицей статуса и не требует использования магического числа (или идентификатора GUID). Использование пользовательских функций IMO - плохая практика. Помимо влияния на производительность, ни один разработчик никогда не ожидал бы, что UDF будут использоваться таким образом, что нарушает критерии наименьшего удивления. Вы почти вынуждены иметь UDF для каждого постоянного значения ; в противном случае, что вы передаете в функцию: имя? магическая ценность? Если имя, вы можете сохранить имя в таблице и использовать его непосредственно в запросе. Если магическое значение, вы вернулись к исходной проблеме.
Вы также можете добавить в свою таблицу статуса дополнительные поля, которые действуют как уникальные маркеры или групперы для значений статуса. Например, если вы добавляете поле isLoaded в таблицу статуса, запись 87 может быть единственной с установленным значением поля, и вы можете проверить значение поля isLoaded вместо жестко запрограммированного 87 или описания статуса.