将存储过程输入参数分配给局部变量是否有助于优化查询? [英] Does assigning stored procedure input parameters to local variables help optimize the query?

问题描述

我有一个需要 5 个输入参数的存储过程.该过程有点复杂，执行大约需要 2 分钟.我正在优化查询.

那么，我的问题是，将输入参数分配给局部变量然后在过程中使用局部变量总是有帮助吗?

如果是这样，它有什么帮助?

解决方案

我不会尝试解释参数嗅探的全部细节，但简而言之，不，它总是帮助(并且它会妨碍).

想象一个有主键和索引日期列(A)的表(T)，表中有 1,000 行，400 行具有相同的 A 值(假设今天是 20130122)，剩下的 600 行是接下来的 600 天，所以每个日期只有 1 条记录.

这个查询:

SELECT *从T哪里 A = '20130122';

将产生不同的执行计划:

SELECT *从T哪里 A = '20130123';

由于统计数据表明将返回 1,000 行中的前 400 行，优化器应该认识到表扫描将比书签查找更有效，而第二个只会产生 1 行，因此书签查找效率会更高.

现在，回到您的问题，如果我们将此作为一个程序:

创建程序 dbo.GetFromT @Param 日期作为选择 *从TWHERE A = @Param

然后运行

EXECUTE dbo.GetFromT '20130122';--400 行

将使用带有表扫描的查询计划，如果您第一次运行它，您使用20130123"作为参数，它将存储书签查找计划.在重新编译程序之前，计划将保持不变.做这样的事情:

CREATE PROCEDURE dbo.GetFromT @Param VARCHAR(5)作为声明@Param2 VARCHAR(5) = @Param;选择 *从T哪里 A = @Param2

然后运行:

EXECUTE dbo.GetFromT '20130122';

虽然过程是一次性编译的，但是流程不正常，所以第一次编译时创建的查询计划不知道@Param2会变成和@param一样，所以优化器(不知道怎么做)许多行预期)将假设将返回 300 (30%)，因此将认为表扫描比书签查找更有效.如果您使用20130123"作为参数运行相同的过程，它将产生相同的计划(无论它首先使用什么参数调用)，因为统计信息不能用于未知值.因此，为 '20130122' 运行此过程会更有效，但对于所有其他值，其效率将低于没有本地参数的情况(假设首先调用没有本地参数的过程，除了 '20130122')

<小时>

要演示的一些查询，以便您可以自己查看执行计划

创建架构和示例数据

CREATE TABLE T (ID INT IDENTITY(1, 1) PRIMARY KEY, A DATE NOT NULL, B INT,C INT, D INT, E INT);在 T (A) 上创建非聚集索引 IX_T；插入 T(A、B、C、D、E)SELECT TOP 400 CAST('20130122' AS DATE), number, 2, 3, 4FROM Master..spt_valuesWHERE 类型 = 'P'联合所有SELECT TOP 600 DATEADD(DAY, number, CAST('20130122' AS DATE)), number, 2, 3, 4FROM Master..spt_valuesWHERE 类型 = 'P';去创建程序 dbo.GetFromT @Param 日期作为选择 *从TWHERE A = @Param去创建程序 dbo.GetFromT2 @Param 日期作为声明 @Param2 日期 = @Param;选择 *从T哪里 A = @Param2去

运行过程(显示实际执行计划):

EXECUTE GetFromT '20130122';执行 GetFromT '20130123';执行 GetFromT2 '20130122';执行 GetFromT2 '20130123';去EXECUTE SP_RECOMPILE GetFromT;EXECUTE SP_RECOMPILE GetFromT2;去执行 GetFromT '20130123';执行 GetFromT '20130122';执行 GetFromT2 '20130123';执行 GetFromT2 '20130122';

您将看到第一次编译 GetFromT 时它使用了表扫描，并在使用参数 '20130122' 运行时保留了这一点，GetFromT2 也使用了一个表扫描并保留20130122"的计划.

在程序设置为重新编译并再次运行后(注意以不同的顺序)，GetFromT 使用书签循环，并保留20130122"的计划，尽管之前认为表扫描是一个更合适的计划.GetFromT2 不受订单影响，与重新编译前的计划相同.

所以，总而言之，这取决于数据的分布、索引、重新编译的频率，以及过程是否会从使用局部变量中受益的运气.它当然不会总是帮助.

<小时>

希望我已经阐明了使用本地参数、执行计划和存储过程编译的效果.如果我完全失败了，或者错过了一个关键点，可以在这里找到更深入的解释:

http://www.sommarskog.se/query-plan-mysteries.html

I have a stored procedure that takes 5 input parameters. The procedure is a bit complicated and takes around 2 minutes to execute. I am in process of optimizing query.

So, my question is, does it always help to assign input parameters to local variables and then use local variables in the procedure?

If so, how does it help?

解决方案

I will not try and explain the full details of parameter sniffing, but in short, no it does not always help (and it can hinder).

Imagine a table (T) with a primary key and an indexed Date column (A), in the table there are 1,000 rows, 400 have the same value of A (lets say today 20130122), the remaining 600 rows are the next 600 days, so only 1 record per date.

This query:

SELECT *
FROM T
WHERE A = '20130122';

Will yield a different execution plan to:

SELECT *
FROM T
WHERE A = '20130123';

Since the statistics will indicate that for the first 400 out of 1,000 rows will be returned the optimiser should recognise that a table scan will be more efficient than a bookmark lookup, whereas the second will only yield 1 rows, so a bookmark lookup will be much more efficient.

Now, back to your question, if we made this a procedure:

CREATE PROCEDURE dbo.GetFromT @Param DATE
AS
    SELECT *
    FROM T
    WHERE A = @Param

Then run

EXECUTE dbo.GetFromT '20130122'; --400 rows

The query plan with the table scan will be used, if the first time you run it you use '20130123' as a parameter it will store the bookmark lookup plan. Until such times as the procedure is recompiled the plan will remain the same. Doing something like this:

CREATE PROCEDURE dbo.GetFromT @Param VARCHAR(5)
AS
    DECLARE @Param2 VARCHAR(5) = @Param;
    SELECT *
    FROM T
    WHERE A = @Param2

Then this is run:

EXECUTE dbo.GetFromT '20130122';

While the procedure is compiled in one go, it does not flow properly, so the query plan created at the first compilation has no idea that @Param2 will become the same as @param, so the optimiser (with no knowledge of how many rows to expect) will assume 300 will be returned (30%), as such will deem a table scan more efficient that a bookmark lookup. If you ran the same procedure with '20130123' as a parameter it would yield the same plan (regardless of what parameter it was first invoked with) because the statistics cannot be used for an unkonwn value. So running this procedure for '20130122' would be more efficient, but for all other values would be less efficient than without local parameters (assuming the procedure without local parameters was first invoked with anything but '20130122')

---

Some queries to demonstate so you can view execution plans for yourself

Create schema and sample data

CREATE TABLE T (ID INT IDENTITY(1, 1) PRIMARY KEY, A DATE NOT NULL, B INT,C INT, D INT, E INT);

CREATE NONCLUSTERED INDEX IX_T ON T (A);

INSERT T (A, B, C, D, E)
SELECT  TOP 400 CAST('20130122' AS DATE), number, 2, 3, 4 
FROM    Master..spt_values 
WHERE   type = 'P'
UNION ALL
SELECT TOP 600 DATEADD(DAY, number, CAST('20130122' AS DATE)), number, 2, 3, 4 
FROM    Master..spt_values 
WHERE   Type = 'P';
GO
CREATE PROCEDURE dbo.GetFromT @Param DATE
AS
    SELECT *
    FROM T
    WHERE A = @Param
GO
CREATE PROCEDURE dbo.GetFromT2 @Param DATE
AS
    DECLARE @Param2 DATE = @Param;
    SELECT *
    FROM T
    WHERE A = @Param2
GO

Run procedures (showing actual execution plan):

EXECUTE GetFromT '20130122';
EXECUTE GetFromT '20130123';
EXECUTE GetFromT2 '20130122';
EXECUTE GetFromT2 '20130123';
GO
EXECUTE SP_RECOMPILE GetFromT;
EXECUTE SP_RECOMPILE GetFromT2;
GO
EXECUTE GetFromT '20130123';
EXECUTE GetFromT '20130122';
EXECUTE GetFromT2 '20130123';
EXECUTE GetFromT2 '20130122';

You will see that the first time GetFromT is compiled it uses a table scan, and retains this when run with the parameter '20130122', GetFromT2 also uses a table scan and retains the plan for '20130122'.

After the procedures have been set for recompilation and run again (note in a different order), GetFromT uses a bookmark loopup, and retains the plan for '20130122', despite having previously deemed that an table scan is a more approprate plan. GetFromT2 is unaffected by the order and has the same plan as before the recompliateion.

So, in summary, it depends on the distribution of your data, and your indexes, your frequency of recompilation, and a bit of luck as to whether a procedure will benefit from using local variables. It certainly does not always help.

---

Hopefully I have shed some light on the effect of using local parameters, execution plans and stored procedure complilation. If I have failed completely, or missed a key point a much more in depth explanation can be found here:

http://www.sommarskog.se/query-plan-mysteries.html

这篇关于将存储过程输入参数分配给局部变量是否有助于优化查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！