参数不执行，以及硬编码值(Parameter doesn't perform as well

我有一个可怕的执行存储过程。当我声明一个变量，设置它的值，然后在where子句中的语句需要一个多小时才能运行在使用它。当我硬编码在不到一秒钟的运行where子句中的变量。

我开始寻找到什么是通过执行计划不妥。它看起来像当我尝试通过它的一些声明的变量执行计划包装箱一些哈希匹配，因为它从一个使用UNION和公用表表达式的视图选择值。

/*************   Begin of Stored Procedure ***************/
CREATE PROCEDURE GetFruit
  @ColorId bigint,
  @SeasionId bigint
WITH RECOMPILE
AS
BEGIN

SELECT
    A.Name
FROM
    [Apple_View] A   /* This is the view down below */
    INNER JOIN [Fruit] F
        ON ( F.ColorId = @ColorId
            AND A.FruitId = F.FruitId)          
WHERE
    (A.ColorId = @ColorId
    AND 
    A.SeasonId = @SeasonId)

END
/************* End of Stored Procedure   ***************/

/************* Begin of View   ***************/
WITH Fruits (FruitId, ColorId, SeasonId) AS
(
    -- Anchor member
    SELECT
        F.FruitId
        ,F.ColorId
        ,F.SeasonId
    FROM
        ((  
            SELECT DISTINCT
                EF.FruitId
                ,EF.ColorId
                ,EF.SeasonId
                ,EF.ParentFruitId
            FROM
                ExoticFruit EF
                INNER JOIN Fruit FR
                    ON FR.FruitId = EF.FruitId
        UNION
            SELECT DISTINCT
                SF.FruitId
                ,SF.ColorId
                ,SF.SeasonId
                ,SF.ParentFruitId               
            FROM
                StinkyFruit SF
                INNER JOIN Fruit FR
                    ON FR.FruitId = SF.FruitId
        UNION
            SELECT DISTINCT
                CF.FruitId
                ,CF.ColorId
                ,CF.SeasonId
                ,CF.ParentFruitId
            FROM
                CrazyFruit CF
                INNER JOIN Fruit FR
                    ON FR.FruitId = CF.FruitId

            )) f

    UNION ALL

    -- Recursive Parent Fruit
    SELECT 
        FS.FruitId
        ,FS.ColorId
        ,FS.SeasonId
        ,FS.ParentFruitId
    FROM
        Fruits FS
        INNER JOIN MasterFruit MF
            ON  MF.[ParentFruitId] = fs.[FruitId]
)

SELECT DISTINCT
    FS.FruitId
    ,FS.ColorId
    ,FS.SeasonId
    FROM
        Fruits FS

/************* End of View   ***************/


/* To Execute */
EXEC GetFruit 1,3

如果我使用的设定值运行存储过程花费了一个多小时，这里是执行计划。

如果我运行存储过程移除声明和设定值，只是Where子句设置为在它不到一秒钟的运行，这里下面的语句是执行计划：

WHERE(A.ColorId = 1 AND  A.SeasonId = 3)

请注意硬编码的变量是如何使用索引，而首先使用哈希集合。这是为什么？为什么从该声明的变量工作不同的where子句中的硬编码值？

-------这就是最后用@ user1166147的帮助下进行------

我改变了存储过程使用sp_executesql的。

CREATE PROCEDURE GetFruit
  @ColorId bigint,
  @SeasionId bigint
WITH RECOMPILE
AS
BEGIN

DECLARE @SelectString nvarchar(max)

SET @SelectString = N'SELECT
    A.Name
FROM
    [Apple_View] A   /* This is the view down below */
    INNER JOIN [Fruit] F
        ON ( F.ColorId = @ColorId
            AND A.FruitId = F.FruitId)          
WHERE
    (A.ColorId = ' + CONVERT(NVARCHAR(MAX), @ColorId) + '
    AND 
    A.SeasonId = ' + CONVERT(NVARCHAR(MAX), @SeasonId) + ')'

EXEC sp_executesql @SelectString

END

Answer 1:

编辑总结每从Damien_The_Unbeliever的请求

我们的目标是获得有关变量的值最好/最信息SQL之前创建的计划，一般参数嗅探做到这一点。可能有参数嗅探在这种情况下是“禁用”的一个原因。如果没有看到实际的代码更好的表现，我们真的不能说什么解决的办法是，或者为什么这个问题的存在。尝试下面的事情，迫使受灾地区使用产生实际值计划。

更多的细节 * 长版*

这是您的实际存储过程？你有默认值的参数？如果是这样，它们是什么？

参数嗅探可以帮助 - 但它必须具有的典型参数值来创建计划好了，如果没有，也不会真正帮助或将创建基于断非典型参数值的一个不错的计划。所以，如果一个变量的空默认值，或者是不是第一次它运行的典型值和编制计划的值 - 它创建了一个不错的计划。

如果别人写了这个存储过程 - 他们可能有故意“禁用”参数有一个原因，局部变量嗅探。业务规则可能需要这些可变的结构。

我们的目标是获得有关变量的值最好/最信息SQL之前创建的计划，一般参数嗅探做到这一点。但有些事情，可以使负面影响性能，这也许可以解释为什么它被“禁用”。它仍然看起来正在用的参数或不典型值不足够的信息仍然创建的计划 - 通过参数嗅探与否。

尝试调用存储过程的内部查询与使用sp_executesql的执行受影响的查询，迫使其产生与实际变量方面的一项计划，看看情况是否好转。这可能是你的解决方案，如果你必须有这种不规则的参数值 - 创建运行受影响的部分存储的特效，并从存储过程中后来打电话给他们 - 变量已经收到了典型值之后。

如果没有看到实际的代码更好的表现，这是很难看到的问题是什么。希望这个信息将帮助 -

Answer 2:

您可以强制它的基础上，你可以更清楚的典型值来优化您的查询。为了您的原始查询添加以下内容：

OPTION (OPTIMIZE FOR(@ColorId = 1, @SeasionId = 3))

它必须不带动态SQL类似的效果。如果你不知道的典型值，可以使优化嗅探他们：

OPTION (OPTIMIZE FOR UNKNOWN)

再次，没有动态SQL

文章来源: Parameter doesn't perform as well as hard coding the value