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

  • Статья

Конструирование структур планов для параметризованных запросов

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

  • запрос передан на обработку при помощи хранимой процедуры sp_executesql;

  • в базе данных активирована принудительная параметризация, при этом параметризуются все допустимые запросы;

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

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

SELECT pi.ProductID, SUM(pi.Quantity) AS Total 
FROM Production.ProductModel pm INNER JOIN Production.ProductInventory pi ON pm.ProductModelID = pi.ProductID 
WHERE pi.ProductID = 101 
GROUP BY pi.ProductID, pi.Quantity 
HAVING sum(pi.Quantity) > 50;

SELECT pi.ProductID, SUM(pi.Quantity) AS Total 
FROM Production.ProductModel pm INNER JOIN Production.ProductInventory pi ON pm.ProductModelID = pi.ProductID 
WHERE pi.ProductID = 101 
GROUP BY pi.ProductID, pi.Quantity 
HAVING sum(pi.Quantity) > 100;

Чтобы создать структуру плана для параметризованных запросов, создайте структуру плана типа SQL и укажите параметризованную форму запроса в хранимой процедуре sp_create_plan_guide.

Например чтобы получить параметризованную форму одного из запросов из предыдущего примера и создать для него структуру плана, заставляющую оптимизатор использовать хэш-соединение, выполните следующее:

  1. Получите параметризованную форму запроса, выполнив хранимую процедуру sp_get_query_template.

  2. Если SQL Server еще не параметризовал запрос с использованием хранимой процедуры sp_executesql или параметра PARAMETERIZATION FORCED базы данных, создайте для выполнения принудительной параметризации структуру плана типа TEMPLATE.

  3. Создайте для параметризованного запроса структуру плана типа SQL.

Следующий пакет выполняет эти шаги:

--Obtain the paramaterized form of the query:
DECLARE @stmt nvarchar(max);
DECLARE @params nvarchar(max);
EXEC sp_get_query_template N'SELECT pi.ProductID, SUM(pi.Quantity) AS Total 
    FROM Production.ProductModel pm 
    INNER JOIN Production.ProductInventory pi 
        ON pm.ProductModelID = pi.ProductID 
    WHERE pi.ProductID = 101 
    GROUP BY pi.ProductID, pi.Quantity 
    HAVING SUM(pi.Quantity) > 50',
@stmt OUTPUT, 
@params OUTPUT;
--Force parameterization of the query. (This step is only required
--if the query is not already being parameterized.)
EXEC sp_create_plan_guide N'TemplateGuide1', 
    @stmt, 
N'TEMPLATE', 
NULL, 
@params, 
N'OPTION(PARAMETERIZATION FORCED)';
--Create a plan guide on the parameterized query
EXEC sp_create_plan_guide N'GeneralizedGuide1', 
@stmt, 
N'SQL', 
NULL, 
@params, 
N'OPTION(HASH JOIN)';

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