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

  • Статья

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

Использование параметров, включая маркеры параметров в приложениях ADO, OLE DB и ODBC, может повысить уровень использования планов выполнения.

Примечание по безопасностиПримечание по безопасности

Использование параметров и маркеров параметров для хранения введенных конечными пользователями значений является более безопасным, чем сцепление значений в строку, которая затем выполняется методом API доступа к данным, инструкцией EXECUTE либо хранимой процедурой sp_executesql.

Единственная разница между следующими двумя инструкциями SELECT — в значениях, сравниваемых в предложении WHERE:

SELECT * 
FROM AdventureWorks2008R2.Production.Product 
WHERE ProductSubcategoryID = 1;

SELECT * 
FROM AdventureWorks2008R2.Production.Product 
WHERE ProductSubcategoryID = 4;

Единственная разница между планами выполнения для данных запросов — в значении, хранимом для сравнения со столбцом ProductSubcategoryID. В то время как выявление факта формирования инструкциями одного и того же плана и повторного его использования является основной задачей SQL Server, SQL Server не всегда может это обнаружить в сложных инструкциях SQL.

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

  • В языке Transact-SQL используйте процедуру sp_executesql:

    DECLARE @MyIntParm INT
SET @MyIntParm = 1
EXEC sp_executesql
  N'SELECT * 
  FROM AdventureWorks2008R2.Production.Product 
  WHERE ProductSubcategoryID = @Parm',
  N'@Parm INT',
  @MyIntParm

    Этот метод рекомендуется для скриптов языка Transact-SQL, хранимых процедур и триггеров, динамически формирующих инструкции SQL.

  • В технологиях ADO, OLE DB и ODBC используются маркеры параметров. Маркеры параметров представляют собой знаки вопроса (?), заменяющие константу в инструкции SQL и привязываемые к программной переменной. Например, в приложении ODBC можно сделать следующее:

    • использовать параметр SQLBindParameter для привязки целочисленной переменной к первому маркеру параметра в инструкции SQL;

    • поместить целочисленное значение в переменную;

    • выполнить инструкцию, указав маркер параметра (?):

      SQLExecDirect(hstmt, 
  "SELECT * 
  FROM AdventureWorks2008R2.Production.Product 
  WHERE ProductSubcategoryID = ?",
  SQL_NTS);

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

  • Чтобы проектировать хранимые процедуры, использующие указанные разработчиком параметры.

Если структура приложения не предусматривает явной подготовки параметров, можно воспользоваться оптимизатором запросов SQL Server для автоматической параметризации некоторых запросов с использованием установленного по умолчанию поведения Простая параметризация. В качестве альтернативы можно заставить оптимизатор запросов учитывать параметризацию всех запросов к базе данных путем установки параметра PARAMETERIZATION инструкции ALTER DATABASE в значение FORCED. Дополнительные сведения см. в разделе Принудительная параметризация.

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

SELECT * FROM Person.Address
WHERE AddressID = 1 + 2;

Однако он может быть параметризован согласно правилам простой параметризации. В случае неуспешной попытки принудительной параметризации впоследствии производятся попытки использования простой параметризации.