不分配存储过程的输入参数，局部变量帮助优化查询？不分配存储过程的输入参数，局部变量帮助优化查询？(D

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

所以，我的问题是，它总是帮助分配输入参数，局部变量，然后在程序中使用局部变量？

如果是这样，它是如何帮助？

Answer 1:

我不会试图解释参数嗅探的全部细节，但总之，没有它并不总是帮助（它可以阻碍）。

想象一下，一个表（T）与主键和索引的日期列（A），表中有1000行，400有一个相同的值（可以说今天20130122），其余的600行是未来600天，所以只有每日期1个纪录。

这个查询：

SELECT *
FROM T
WHERE A = '20130122';

将产生不同的执行计划：

SELECT *
FROM T
WHERE A = '20130123';

由于统计数据将表明，第一个400出的1000个行将返回的优化器应该认识到，一个表扫描会比书签查找更有效，而第二只产生1行，所以书签查找将大大更高效。

现在，回到你的问题，如果我们做了这样一个过程：

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

然后运行

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

与表扫描的查询计划将被使用，如果你运行它第一次使用“20130123”作为参数，它将存储书签查找计划。直到时间的重新编译过程的计划将保持不变。做这样的事情：

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

那么这是运行：

EXECUTE dbo.GetFromT '20130122';

虽然过程一气呵成编译，它不能正常流动，因此在第一次编译生成的查询计划具有不知道@参数2将成为同@param，所以优化器（没有多少行的知识期望的）将假定300将被返回（30％），因为这样会认为表扫描更高效，书签查找。如果你跑了“20130123”相同的过程作为一个参数，将产生相同的计划（不管是什么参数，它是第一个与调用），因为统计数据不能被用于一个unkonwn值。因此，运行此程序“20130122”会更有效，但对于所有其他值会比没有本地参数低效率的（假设没有本地参数的过程首先用任何调用，只是“20130122”）

一些查询demonstate以便您可以查看执行计划为自己

创建模式和样本数据

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

运行程序（显示实际执行计划）：

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';

你会看到，在第一时间GetFromT编译它采用的是表扫描，而当与参数“20130122”，跑保留了这一GetFromT2还采用了表扫描并保存为“20130122”的计划。

该程序已被定为重新编译后，再次运行（注意以不同的顺序）， GetFromT使用书签loopup，并保留了“20130122”的计划，尽管之前曾认为，一个表扫描是一个比较approprate计划。 GetFromT2是由订单的影响，并且具有相同的计划，作为recompliateion之前。

所以，总的来说，这取决于你的数据，你的索引，你重新编译的频率，和一点点运气，以一个程序是否会使用局部变量受益的分布。这当然并不总是帮助。

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

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

Answer 2:

它帮助。

以下链接包含有关参数嗅探更多细节。

http://blogs.msdn.com/b/turgays/archive/2013/09/10/parameter-sniffing-problem-and-workarounds.aspx

http://sqlperformance.com/2013/08/t-sql-queries/parameter-sniffing-embedding-and-the-recompile-options

当您执行与首次参数的SP，查询优化器创建一个基于参数值的查询计划。查询优化器使用统计数据的特定值来决定最佳的查询计划。但是基数问题，会影响这一点。其中如果执行不同的参数值相同SP先前生成的查询计划可能不是最好的计划手段。

通过分配参数局部变量，我们隐藏查询优化器的参数值。所以它创造了一般情况下，查询计划。