Поделиться через

  • Статья

Использование sp_executesql

Для выполнения строки рекомендуется пользоваться не инструкцией EXECUTE, а хранимой процедурой sp_executesql. Она обладает большей функциональностью, поскольку поддерживает подстановку параметров, а также более эффективна, чем инструкция EXECUTE, поскольку создает планы выполнения, которые в SQL Server с большей вероятностью будут использоваться повторно.

Автономные пакеты

Хранимая процедура sp_executesql и инструкция EXECUTE производят выполнение строки как автономного пакета, то есть SQL Server скомпилирует инструкции Transact-SQL, находящиеся в строке, в план выполнения, отдельный от плана выполнения пакета, содержащего вызов процедуры sp_executesql или инструкции EXECUTE. Для автономных пакетов применяются следующие правила.

  • Инструкции Transact-SQL в sp_executesql или строке EXECUTE не компилируются в план выполнения до выполнения процедуры sp_executesql или инструкции EXECUTE. Синтаксический анализ строк и их проверка на ошибки не происходит до их выполнения. Имена, ссылки на которые находятся в строках, не разрешаются до выполнения строк.

  • Инструкции Transact-SQL в выполняемых строках не обладают доступом к каким-либо переменным, объявленным в содержащем sp_executesql или инструкцию EXECUTE пакете. Пакет, содержащий sp_executesql или инструкцию EXECUTE, не обладает доступом к переменным или местным курсорам, объявленным в выполняемой строке.

  • Если в выполняемой строке находится инструкция USE, меняющая контекст базы данных, то эти изменения будут действовать только до конца выполнения процедуры sp_executesql или инструкции EXECUTE.

Выполнение следующих двух пакетов иллюстрирует следующие моменты:

/*Show not having access to variables from the calling batch. */
DECLARE @CharVariable CHAR(3);
SET @CharVariable = 'abc';
/* sp_executesql fails because @CharVariable has gone out of scope. */
EXECUTE sp_executesql N'PRINT @CharVariable';
GO

/* Show database context resetting after sp_executesql finishes. */
USE master;
GO
EXECUTE sp_executesql N'USE AdventureWorks2008R2;'
GO
/* This statement fails because the database context
   has now returned to master. */
SELECT * FROM Sales.Store;
GO

Замена значений параметров

Процедура sp_executesql поддерживает замену значений для всех параметров, определенных в строке Transact-SQL, а инструкция EXECUTE такую поддержку не обеспечивает. Поэтому строки Transact-SQL, созданные процедурой sp_executesql, имеют большее сходство, чем строки, создаваемые инструкцией EXECUTE. Оптимизатор запросов SQL Server, возможно, сопоставит инструкции Transact-SQL из процедуры sp_executesql с планами выполнения из выполнявшихся ранее инструкций, обойдя тем самым дополнительную нагрузку по созданию нового плана выполнения.

В инструкции EXECUTE значения всех параметров должны быть преобразованы в текст или Юникод и включены в строку Transact-SQL.

Если инструкция выполняется многократно, то для каждого выполнения должна создаваться полностью новая строка Transact-SQL, даже когда единственными отличиями являются заданные для параметров значения. Это приводит к появлению дополнительной нагрузки, поскольку:

  • способность оптимизатора запросов SQL Server сопоставлять новую строку Transact-SQL с уже существующим планом выполнения снижается из-за постоянно изменяющихся значений параметров в тексте строки, особенно в сложных инструкциях Transact-SQL;

  • для каждого выполнения строка должна полностью создаваться заново;

  • значения параметров (отличных от параметров с символьными типом данных или типом данных Юникод) должны приводится к символьному формату или формату Юникод при каждом выполнении.

Процедура sp_executesql поддерживает отдельную от строки Transact-SQL настройку значений параметров:

DECLARE @IntVariable INT;
DECLARE @SQLString NVARCHAR(500);
DECLARE @ParmDefinition NVARCHAR(500);

/* Build the SQL string one time. */
SET @SQLString =
     N'SELECT * FROM AdventureWorks2008R2.Sales.Store WHERE SalesPersonID = @SalesID';
/* Specify the parameter format one time. */
SET @ParmDefinition = N'@SalesID int';

/* Execute the string with the first parameter value. */
SET @IntVariable = 275;
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @SalesID = @IntVariable;
/* Execute the same string with the second parameter value. */
SET @IntVariable = 276;
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @SalesID = @IntVariable;

Процедура sp_executesql предлагает следующие дополнительные преимущества.

  • Так как фактический текст инструкции Transact-SQL между выполнениями не изменяется, оптимизатор запросов должен сопоставить инструкцию Transact-SQL из второго выполнения с планом выполнения, созданным для первого выполнения. Следовательно, компиляция второй инструкции SQL Server не обязательна.

  • Строка Transact-SQL строится только один раз.

  • Целочисленный параметр определен в собственном формате. Преобразование в Юникод не требуется.

    ПримечаниеПримечание

    Имена объектов в строке инструкции должны полностью подходить для SQL Server при повторном использовании плана выполнения.

Повторное использование планов выполнения

В предыдущих версиях SQL Server единственным способом повторного использования планов выполнения было определение инструкций Transact-SQL в качестве хранимых процедур и выполнение этих процедур в приложениях. Это создавало дополнительную административную нагрузку для этих приложений. Хранимая процедура sp_executesql помогает снизить издержки и позволяет SQL Server повторно использовать планы выполнения. Ей можно пользоваться вместо хранимых процедур при многократном выполнении инструкций Transact-SQL, когда единственное различие состоит в значениях параметров. Так как сами инструкции Transact-SQL остаются постоянными, а изменяются только значения параметров, то оптимизатору запросов SQL Server предпочтительнее использовать созданный им для первого выполнения план выполнения.

В следующем примере для каждой базы данных на сервере, за исключением четырех баз данных, создается и выполняется инструкция DBCC CHECKDB.

USE master;
GO
SET NOCOUNT ON;
GO
DECLARE AllDatabases CURSOR FOR
SELECT name FROM sys.databases WHERE database_id > 4
OPEN AllDatabases;

DECLARE @DBNameVar NVARCHAR(128);
DECLARE @Statement NVARCHAR(300);

FETCH NEXT FROM AllDatabases INTO @DBNameVar;
WHILE (@@FETCH_STATUS = 0)
BEGIN
   PRINT N'CHECKING DATABASE ' + @DBNameVar;
   SET @Statement = N'USE ' + @DBNameVar + CHAR(13)
      + N'DBCC CHECKDB (' + @DBNameVar + N')' + N'WITH PHYSICAL_ONLY';
   EXEC sp_executesql @Statement;
   PRINT CHAR(13) + CHAR(13);
   FETCH NEXT FROM AllDatabases INTO @DBNameVar;
END;

CLOSE AllDatabases;
DEALLOCATE AllDatabases;
GO
SET NOCOUNT OFF;
GO

Драйвер SQL Server ODBC использует процедуру sp_executesql для реализации SQLExecDirect, когда выполняющаяся инструкция Transact-SQL содержит связанные маркеры параметров. Это распространяет преимущества процедуры sp_executesql на все приложения, которые пользуются стандартными функциями ODBC или API-интерфейсов, работающих через ODBC (например RDO). Производительность существующих приложений ODBC после переноса на SQL Server автоматически повышается безо всякой переработки кода. Единственным исключением является то, что процедура sp_executesql не используется с параметрами времени выполнения функции. Дополнительные сведения см. в разделе Использование параметров инструкции.

Поставщик OLE DB для собственного клиента SQL Server также использует процедуру sp_executesql, чтобы реализовать прямое выполнение инструкций со связанными параметрами. Использующие OLE DB или ADO приложения также получают преимущества, предоставляемые процедуре sp_executesql без переписывания.