SQL-Ex blog

Определение подготовленного оператора SQL

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

SELECT CustomerName, AccountOpenedDate, StandardDiscountPercentage, PaymentDays FROM Sales.Customers WHERE CustomerID = 5;

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

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

Эта процедура выполняет свою работу, принимая три параметра в следующем порядке:

1. Выходной параметр типа INT - число, используемое для идентификации запроса.


2. Список параметров, определяемый в строке Юникод.


3. Оператор SQL - может ссылаться на переменные, определяемые во втором параметре. Это тоже строка Юникод. Заглавная буква N перед вторым и третьим параметрами означает, что это строки Юникод. Эти параметры являются обязательными.

Ниже приводится тот же запрос, только он будет сохранен как подготовленный оператор для последующего выполнения.

DECLARE @PreparedStatementNumber INT;

EXEC sp_prepare @PreparedStatementNumber OUTPUT,

    N'@CustID INT',

    N'SELECT CustomerName, AccountOpenedDate, StandardDiscountPercentage, PaymentDays FROM Sales.Customers WHERE CustomerID = @CustID;';

 

SELECT @PreparedStatementNumber;

Результатом выполнения этого скрипта будут два набора данных:

1. Пустой результирующий набор, который отвечает выводу запроса. Он может использоваться приложением для построения объекта, который будет впоследствии содержать результаты этого запроса, и


2. Целое число, которое будет использоваться для вызова этого подготовленного оператора SQL. (Для этого примера значение будет равно 1, поскольку это будет первый и единственный подготовленный оператор на этом SPID.)

Вызов подготовленного оператора SQL

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

Вызов процедуры может показаться несколько необычным. Значение 1 является идентификатором подготовленного оператора, а 5 - значение параметра для CustID.

EXEC sp_execute 1, 5;

Вывод этого оператора sp_execute идентичен выводу первого запроса, приведенному в начале этой статьи.



Подготовленный запрос можно выполнять неоднократно с любым подходящим значением параметра CustID.

EXEC sp_execute 1, 7;

EXEC sp_execute 1, 432;

EXEC sp_execute 1, 23;

Ниже пример подготовленного оператора SQL, который имеет два параметра. Замечание: второй параметр sp_prepare содержит две переменных, разделенных запятой. Теперь оператор sp_execute имеет не два, а три параметра.

DECLARE @PreparedStatementNumber INT;

EXEC sp_prepare @PreparedStatementNumber OUTPUT,

    N'@CustID INT, @CustomerName NVARCHAR(100)',

    N'SELECT CustomerName, AccountOpenedDate, StandardDiscountPercentage, PaymentDays FROM Sales.Customers WHERE CustomerID = @CustID OR CustomerName LIKE @CustomerName;';

SELECT @PreparedStatementNumber;



EXEC sp_execute 5, 0, N'Tail%';

Разрушение подготовленного оператора SQL

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

Эта процедура принимает единственный параметр - число, связанное с сохраненным оператором.

exec sp_unprepare 1;

Поиск кода подготовленного оператора SQL

При настройке производительности или отладке приложения, которое использует подготовленный SQL, трассировка или сессия расширенных событий будет полна подобных этим запросов:

EXEC sp_execute 6, 7, 24, '1/1/2005';

EXEC sp_execute 13, 432, 'HELLO';

EXEC sp_execute 4, 'YELLOW', 'MEDIUM';

Обычный вопрос, который задают в связи с подготовленными операторами SQL, звучит так: "Как мне узнать, какие запросы выполнялись этим приложением?"

Не существует ни DMV, ни DBCC, которые помогли бы получить список запросов и соответствующие им номера. Есть только один способ определить это - выполнить трассировку, которая включает объект SP:StmtCompleted, в профайлере или ее эквивалент расширенного события sp_statement_completed.

Вот скриншот трассировки профайлера, которая записывалась при выполнении подготовленного оператора SQL из примера. Только строка SP:StmtCompleted содержит фактический оператор.



А вот вывод сессии расширенного события, которая выполнялась в то же время, что и трассировка профайлера выше. Вывод аналогичен.

О производительности подготовленного SQL

Запросы, выполняемые как подготовленные операторы SQL, оптимизируются не так, как запросы, выполняемые в хранимых процедурах, или запросы ad-hoc. Для небольших наборов данных это не имеет значения. Для запросов, которые выполняют поиск на основе столбцов первичного ключа, например, простой поиск как в самом первом примере этой статьи, это также не имеет значения. Когда объемы данных растут, а запросы включают больше столбцов, это может ухудшить производительность запросов, проблему, которую может оказаться сложно решить.

Альтернативы подготовленному SQL

Подготовленный SQL когда-то был популярной функцией SQL Server, однако сейчас это уже не так. Многие считают этот способ написания кода SQL устаревшим. Этому уже, как правило, не учат на курсах, не пишут в книгах и блогах.

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

Заключительные мысли

Целью этой статьи не было заставить читателей использовать подготовленный SQL как метод программирования. Напротив, цель состояла в том, чтобы убедиться, что читатели, как профессионалы SQL Server, или разработчики, которые захотят реализовать подобную функциональность, понимают эту функцию и то, как она работает, если они столкнутся с ней.

Многие языки программирования приложений пытаются использовать подготовленные операторы по умолчанию. Иногда профессионалы SQL Server получают в наследство приложение, которое использует подготовленный SQL. Так произошло со мной; мне досталось приложение третьей фирмы, использующее подготовленный SQL и которое потребовало усилий в настройке производительности. За многие годы моей профессиональной работы с SQL Server, я впервые столкнулся с запросом подготовленного SQL и должен был быстро с этим разобраться.