Parameter doesn't perform as well as hard coding the value

Question

I have a stored procedure that performs terribly. When I declare a variable, set its value and then use it in the where clause the statement takes over an hour to run. When I hard code the variables in the where clause it runs in less than a second.

I started to look into what was wrong with it through execution plans. It looks like when I try and pass it some declared variables the execution plan crates some Hash Match because it selects values from a view that uses a UNION and a common table expression.

/*************   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

If I run the Stored Procedure using the set values it takes over an hour and here is the execution plan.

If I run the Stored Procedure removing the DECLARE and SET values and just set the Where clause to the following statement it runs in less than a second and here is the execution plan:

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

Notice how the hard coded variables uses indexing while the first uses a hash set. Why is that? Why are hard coded values in the where clause working different from the declared variables?

-------this is what finally performed with the help of @user1166147------

I changed the stored procedure to use 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

EDIT SUMMARY Per a request from Damien_The_Unbeliever

The goal is to get best/most information about the variable value to SQL BEFORE the plan is created, generally parameter sniffing does this. There may be a reason that parameter sniffing was 'disabled' in this case. Without seeing a better representation of the actual code we can't really say what the solution is or why the problem exists. Try the things below to force the affected areas to generate plans using actual values.

*LONG VERSION WITH MORE DETAIL *

Is this your actual stored proc? Do you have default values for your parameters? If so, what are they?

Parameter sniffing can help - but it has to have typical parameters values to create the plan well, and if not, won't really help or will create a bad plan based off of the non typical parameter value. So if a variable has a default value of null or a value that is not a typical value the first time it is run and the plan compiled - it creates a bad plan.

If someone else wrote this sproc - they may have intentionally 'disabled' parameter sniffing with the local variables for a reason. Business rules may require these variable structures.

The goal is to get best/most information about the variable value to SQL BEFORE the plan is created, and generally Parameter Sniffing does this. But there are things that can make it affect performance negatively, and that may be why it is 'disabled'. It still seems like the plan is being created with atypical values for the parameters or not enough info still - using parameter sniffing or not.

Try calling the query inside the sproc with Use sp_executesql to execute the affected queries, forcing it to generate a plan for that area with the actual variables, and see if it's better. This may be your solution if you have to have this sort of irregular parameter value - create stored procs that run the affected parts and call them later from within the stored procedure - after the variable has received a typical value.

Without seeing a better representation of the actual code, it is hard to see what the problem is. Hopefully this info will help -