Полезные привычки при написании операторов T-SQL (часть 2)

TipsMake.com — Чтобы улучшить производительность SQL-сервера и свести к минимуму возможные ошибки для приложения, нам необходимо попрактиковаться в оптимальном написании оператора T-SQL. В первой части статьи вы познакомились с некоторыми полезными приемами, которые помогают серверу сократить количество избыточных операций. Во второй части основное внимание будет уделено тому, как оптимизировать многоцелевые процедуры хранения.

Изображение 1 хороших привычек при написании операторов T-SQL (часть 2)

Часть 1

Универсальная процедура хранения

Прежде чем перейти к вопросу о том, как оптимизировать процедуру хранимой процедуры (SP) на все руки, нам нужно иметь некоторое представление об этом типе процедуры. Процедура многоцелевого хранения — это процедура, которая принимает множество различных параметров, связанных с процедурой. На основе переданных параметров процедура универсального хранилища определяет, какие записи будут возвращены. Ниже приводится пример универсальной процедуры хранения:

СОЗДАТЬ ПРОЦЕДУРУ JackOfAllTrades (@SalesOrderID int = NULL
, @ SalesOrderDetailID int = NULL
, @ CarrierTrackingNumber nvarchar (25) = NULL)
В ВИДЕ
ВЫБРАТЬ * ИЗ AdventureWorks.Sales.SalesOrderDetail
ГДЕ
(SalesOrderID = @SalesOrderID или @SalesOrderID ЕСТЬ NULL)
И (SalesOrderDetailID = @SalesOrderDetailID или
@SalesOrderDetailID ЕСТЬ NULL)
И (CarrierTrackingNumber = @CarrierTrackingNumber или
@CarrierTrackingNumber ЕСТЬ NULL)
ИДТИ

Здесь JackOfAllTrades SP принимает три разных параметра. Все эти параметры имеют значение по умолчанию NULL. Когда значение передается, оно будет использоваться в качестве параметра в предложении WHERE для привязки возвращаемых записей. Каждый параметр в SP используется для создания сложного предложения WHERE, содержащего следующую логику в предложении WHERE для каждого переданного параметра:

(= @PARM или @PARM IS NULL)

Приведенная выше логика показывает, что если @PARM передается ненулевое значение, он свяжет возвращаемую запись, чтобы убедиться, что она равна значению @PARM. Вторая часть этого условия — «@PARM IS NULL». Этот раздел означает, что если @PARM не имеет входного значения (равного NULL), то привязка данных не основана на этом параметре.

Ознакомьтесь с типичным процессом внедрения JackOfAllTrades SP. Предположим, мы выполняем SP с помощью следующей команды:

EXEC JackOfAllTrades @SalesOrderID = 43659

При запуске команды диаграмма выполнения выглядит так:

Изображение 2 хороших привычек при написании операторов T-SQL (часть 2)

Здесь вы можете видеть, что для каждого переданного параметра сервер решает использовать операцию «сканирование индекса». Оператор SELECT SP связывает уникальный столбец @SalesOrderID — часть ключа индекса кластера. Вы можете подумать, что SQL-сервер достаточно умен, чтобы понять, что обработка многоцелевой хранимой процедуры с помощью операции «поиск по индексу» происходит быстрее, чем обработка индекса кластера. Но, как мы видим на схеме реализации, SQL-сервер не так уж и умен. Почему?

Когда сервер видит условие «@PARM IS NULL», это константа для сервера SQL. Таким образом, сервер считает, что нет полезного индекса для обработки условия «(= @ PARM1 или @ PARM1 IS NULL)», потому что константа находится в предложении WHERE. Вот почему SQL-сервер решает использовать операцию «сканирование индекса» для решения проблемы. Чем больше процедур хранения с несколькими параметрами, тем ниже производительность из-за влияния количества операций сканирования, необходимых для каждого переданного параметра.

Оптимизировать многоцелевые процедуры хранения

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

Как упоминалось выше, настоящая проблема с универсальной процедурой хранения заключается в том, что для каждого параметра необходимо иметь условие «ИЛИ», чтобы проверить, является ли переданный параметр ПУСТО (NULL). Если мы сможем удалить этот запрос, SQL-сервер сможет запланировать использование операции «поиск по индексу». Так как же убрать условие «@PARM IS NULL»? Ответ — использовать параметризованный динамический SQL.

Вы можете подумать, что я собираюсь открыть путь для SQL-инъекции в свое решение. Однако мы будем создавать только динамический код SQL для передачи параметров SP в другой системный SP ‘sp_executesql’. Этот SP будет использовать параметры в динамическом коде SQL, который мы создаем.

Система sp_executesql позволяет вам разрабатывать оператор T-SQL, содержащий параметры, и позволяет вам определять и передавать значения параметров в динамический SQL, передавая параметры SP sp_executesql при запуске этого SP. Выполняемый таким образом оператор T-SQL часто называют оцифрованным SQL. Есть много причин использовать оцифрованный SQL, но в рамках этой статьи мы сосредоточимся только на том, как использовать оцифрованный SQL для повышения производительности многоцелевых процедур хранения. . Ниже приведен код для создания переписанной многоцелевой хранимой процедуры с использованием параметризованного динамического SQL:

СОЗДАТЬ ПРОЦЕДУРУ JackOfAllTrades_V2 (@SalesOrderID int = NULL
, @ SalesOrderDetailID int = NULL
, @ CarrierTrackingNumber nvarchar (25) = NULL)
В ВИДЕ
ОБЪЯВИТЬ @CMD NVARCHAR (макс.)
ОБЪЯВИТЬ @ ГДЕ NVARCHAR (макс.)
НАБОР @CMD = ‘ВЫБРАТЬ * ИЗ AdventureWorks.Sales.SalesOrderDetail’
УСТАНОВИТЬ @ ГДЕ = »
ЕСЛИ @SalesOrderID НЕ НУЖЕН
НАБОР @WHERE = @WHERE + ‘И SalesOrderID = @SalesOrderID’
ЕСЛИ @SalesOrderDetailID НЕ НУЖЕН
НАБОР @WHERE = @WHERE + ‘И SalesOrderDetailID = @SalesOrderDetailID’
ЕСЛИ @CarrierTrackingNumber НЕ НУЖНО
НАБОР @WHERE = @WHERE + ‘И CarrierTrackingNumber = @CarrierTrackingNumber’
ЕСЛИ LEN (@WHERE)> 0
НАБОР @CMD = @CMD + ‘WHERE’ + RIGHT (@ WHERE, LEN (@WHERE) — 3)
EXEC sp_executesql @CMD
, N ‘@ SalesOrderID int
, @ SalesOrderDetailID int
, @ CarrierTrackingNumber nvarchar (25) ‘
, @ SalesOrderID = @SalesOrderID
, @ SalesOrderDetailID = @SalesOrderDetailID
, @ CarrierTrackingNumber = @CarrierTrackingNumber

Далее мы углубимся в детали, чтобы помочь вам понять динамическую часть и параметризацию приведенного выше кода. Этот SP начинается с присвоения переменной @CMD оператору SELECT без предложения WHERE. Затем мы присваиваем переменной @WHERE пустую строку. Далее следуют четыре разных оператора IF. Первые три оператора IF проверяют, является ли каждый переданный параметр НЕ ПУСТОЙ. Если параметр НЕ ПУСТО (NOT NULL), мы добавим условие к переменной @WHERE для этого параметра. Поскольку мы проверили и определили, что параметр NOT NULL, нам не нужно добавлять условие IS NULL в предложение WHERE в качестве исходного кода SP, приведенного выше. Вместо этого все, что нам нужно, это добавить условие = @PARM в переменную @WHERE. Последний оператор IF определяет, удовлетворяет ли переменная @WHERE хотя бы одному условию, и если да, то он объединяет переменную @WHERE с переменной @CMD.

Обратите внимание, что переменная @WHERE является динамической частью кода. Но я не помещаю фактический текст параметра в переменную @WHERE, вместо этого просто помещаю ссылку на параметры в условии WHERE. Таким образом, базовый динамический оператор T-SQL включает только исходный оператор SELECT, а предложение WHERE больше не требует условия IS NULL для привязки данных.

Наконец, я использовал SP sp_executesql для выполнения динамических параметризованных операторов T-SQL. Для этого я передаю в системный SP пять параметров. Первый параметр — это динамическая переменная T-SQL @CMD. Второй параметр объявляет все возможные переменные в параметризованном сегменте запроса вместе с их типом данных. С последними тремя параметрами они передаются только системному SP, как если бы они были переданы универсальному хранилищу SP в первой секции. Как видите, я абсолютно не сделал мой динамический SQL-код более уязвимым для атак SQL-инъекций, чем исходный SP. Причина в том, что я не использую фактическое значение параметра для перехода к переменной @WHERE. Я передаю параметры как переменные в динамический SQL только через SP ‘sp_executesql system’.

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

EXEC JackOfAllTrades_V2 @SalesOrderID = 43659

Когда я запустил тест с базой данных AdventureWorks на сервере, я получил следующий план выполнения:

Изображение 3: хорошие привычки при написании операторов T-SQL (часть 2)

Сравнивая эту диаграмму с диаграммой в начале, вы можете увидеть, что она проще и использует операцию «поиск индекса кластера» для обработки SP. Причина, по которой SQL Server может использовать эту операцию, заключается в том, что в динамическом коде SQL больше нет условия «@PARM IS NULL». Поскольку оператор T-SQL был упрощен с использованием динамического SQL и устранения ограничения IS NULL, сервер SQL теперь может предложить более оптимальный план выполнения для универсальной процедуры хранения версии V2. .

В заключение, какого эффекта мы достигли на практике? Помните, что выше мы рассматриваем только записи из таблицы SalesOrderDetail с SalesOrderID, равным 43659. Исходная SP универсального хранилища использует операцию «сканирование индекса» для обработки запроса. Это означает, что он должен прочитать весь индекс по очереди, прежде чем он сможет завершить запрос и вернуть запись, содержащую значение SalesOrderID. И наоборот, универсальная версия хранилища SP V2 может использовать операцию «индексного поиска» с ключом индекса кластера в таблице SalesOrderDetail для быстрого получения определенных записей, содержащих SalesOrderID с 43659. . Оптимальная операция «поиск по индексу» — это гораздо больше, чем «сканирование индекса», но насколько конкретна?

Оценка объема операций ввода-вывода, сэкономленного при использовании версии SP универсального хранилища V2, может быть выполнена разными способами. Мы запустим следующий сегмент T-SQL:

ВКЛЮЧИТЬ СТАТИСТИКУ IO
ИДТИ
EXEC JackOfAllTrades @SalesOrderID = 43659
ИДТИ
EXEC JackOfAllTrades_V2 @SalesOrderID = 43659
ИДТИ

Здесь я использую команду «SET STATISTICS IO ON», чтобы результаты двух выполняющихся в данный момент SP отображали количество операций ввода-вывода, необходимых каждой команде для обработки запроса. Вот полученные результаты:

(Затронуто 12 рядов)
Таблица SalesOrderDetail. Запись номер 1, действительное чтение 264, физическое чтение 0, опережающее чтение 0,
допустимый лоб имеет чтение 0, физическое чтение лобовое 0, переднее чтение лобовое 0.

(Затронута 1 строка (и))

(Затронуто 12 рядов)
Таблица SalesOrderDetail. Проверяет номер 1, действительное чтение 3, физическое чтение 0, опережающее чтение 0,
допустимый лоб имеет чтение 0, физическое чтение лобовое 0, переднее чтение лобовое 0.

(Затронута 1 строка (и))

Глядя на приведенные выше результаты, мы видим, что производительность первого SP универсального хранилища составляет 1 сканирование и 264 логических чтения. Напротив, версия V2 имеет такое же количество сканирований индекса, но для обработки запроса требуется только 3 логических чтения. Экономия ввода-вывода составляет 261. Это число не кажется большим, но, если вам, например, придется вызывать SP туда и обратно в цикле, производительность будет значительно улучшена. Заметно между двумя версиями SP.

Повышение качества ввода-вывода с помощью параметризованного динамического SQL

Прочитав этот раздел, вы должны понять, почему SQL-сервер выдал неэффективную схему реализации. Выше SQL-сервер рассматривал логику «@PARM IS NULL» как константу. Поэтому решено выполнить операцию «сканирования индекса» для обработки первой версии многоцелевой хранимой процедуры. Как известно, сканирование (SCAN) всегда медленнее, чем поиск (SEEK). Переписав версию V2 универсального хранилища SP с использованием T-SQL динамически, я удалил постоянное выражение в предложении WHERE оператора T-SQL. Таким образом, SQL-сервер нашел более правильный метод — использовать операцию «поиск индекса кластера». Если на вашем сайте используется многоцелевая хранимая процедура, попробуйте переписать ее с помощью оцифрованного динамического SQL и подождите, чтобы увидеть, как улучшится производительность.

Похожие записи

Добавить комментарий

Ваш адрес email не будет опубликован.