Параметр работает хуже, чем жесткое кодирование значения.

У меня есть хранимая процедура, которая работает ужасно. Когда я объявляю переменную, устанавливаю ее значение, а затем использую его в предложении 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

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

Если я запускаю хранимую процедуру, удаляя значения DECLARE и SET, и просто устанавливаю в предложении 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