Q将存储过程输入参数分配给局部变量有助于优化查询吗?

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

所以,我的问题是,把输入参数赋给局部变量,然后在过程中使用局部变量总是有帮助的吗?

如果是,有什么帮助?

2013-01-22 21:45:17  Learner
  • 我无法想象@paramDECLARE @param_copy比引用@param更有效……你的执行路径是什么样的? – Brad Christie Jan 22 '13 at 21:47
  • 你是从哪里得到这种想法的?说明来源?我会少担心参数,多关心查询。提供优化建议的执行计划。也许更适合DBA,所以。 – Paul Fleming Jan 22 '13 at 21:51
  • 我会将计划发布到DBA站点。谢谢。 – Learner Jan 22 '13 at 21:58

A回答

  • 1我不会尝试解释参数嗅探的全部细节,但简而言之,不,它不强>总是<强>帮助(它可能会阻碍)。

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

    此查询:

    SELECT *
    FROM T
    WHERE A = '20130122';
    

    将产生不同的执行计划:

    SELECT *
    FROM T
    WHERE A = '20130123';
    

    由于统计将表明,对于1000行中的前400行将返回,优化器应该认识到表扫描将比书签查找更有效,而第二个将只产生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';
    
    虽然过程是一次性编译的,但它不能正常地流,所以第一次编译时创建的查询计划不知道@将变成与@相同的参数,所以不知道要预期的行数(30%)将返回300,这样一来,表扫描就更有效率,书签查找也就更有效了。如果以20130123作为参数运行相同的程序,它将产生相同的计划(不管它最初调用了什么参数),因为统计数据不能用于不可预测的值。因此,为20130122运行这个过程会更有效,但是对于所有其他值都比没有局部参数的效率低(假设没有局部参数的程序首先用20130122来调用)


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

    创建模式和示例数据< /P>

    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使用书签循环,并保留20130122的计划,尽管以前认为表扫描是更合适的计划。{ { }不受订单的影响,且与之前的计划相同。总之,它取决于数据的分布、索引、频率,以及是否使用局部变量来受益于程序。它当然不会总是有帮助。


    希望我对使用本地参数、执行计划和存储过程的效果有了一些了解。如果我完全失败了,或者错过了一个关键点,可以在这里找到更深入的解释:

    < HeRF= =http://www. SOMMASKOG.SE/QuasePosith.Value.html> http://www. SOMMASKO.SE/Quang-Posi谜。GetFromT2

    2013-01-23 00:01:44  GarethD
  • 2

    它确实有帮助。

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

    < HPF= =http://bng./b/turgays/Soviv/2013/09/10/PosivsNoffo.S.ASPXRe=NoFoLLoW> http://blog。MSDN.COM/B/Turgay/Guiviv/2013/09/10/参数嗅探问题和解决方案。ASPX < /A>

    <一个HRFF=http://qLalcx.COM/2013/08/T-QL查询/参数嗅探嵌入和重编译选项Re=NoFoLoLy>http://qLaskal.com/2013/08/T-SQL查询/参数嗅探嵌入和重新编译选项/p>

    当您第一次使用参数执行sp时,查询优化器根据参数的值创建查询计划。查询优化器使用该特定值的统计数据来决定最佳查询计划。但基数问题会影响这一点。这意味着,如果执行同一个SP,参数值不同,先前生成的查询计划可能不是最好的计划。

    通过将参数赋值给局部变量,我们从查询优化器中隐藏参数值。因此,它创建了一般情况下的查询计划。

    这与SP.

    中使用优化未知提示相同。
    2014-10-06 21:50:41  Suneth De Silva
  • 3

    我不这么认为。现代计算机体系结构有足够的高速缓存接近处理器来存储存储过程值。本质上,你可以把它们看作是一个堆栈,它被加载到本地缓存内存中。

    如果有输出参数,那么可能将输入值复制到局部变量将消除间接的一个步骤。然而,第一次执行间接操作时,目标内存将放在本地缓存中,并且可能保留在那里。

    <不,我不认为这是一个重要的优化。

    但是,您总是可以对存储过程的不同变体进行检查,看看这是否有帮助。
    2013-01-22 22:28:35  Gordon Linoff
    • 感谢@gordon提供的信息 – Learner Jan 22 '13 at 22:34