Why does a query slow down drastically if in the WHERE clause a constant is replaced by a parameter (having the same value)?

Question

I have a recursive query which executes very fast if the WHERE clause contains a constant but becomes very slow if I replace the constant with a parameter having the same value.

Query #1 - with constant

;WITH Hierarchy (Id, ParentId, Data, Depth)
AS
( SELECT Id, ParentId, NULL AS Data, 0 AS Depth
  FROM Test
  UNION ALL
  SELECT h.Id, t.ParentId, COALESCE(h.Data, t.Data), Depth + 1 AS Depth
  FROM Hierarchy h
       INNER JOIN Test t ON t.Id = h.ParentId
)
SELECT *
FROM Hierarchy
WHERE Id = 69

Query #2 - with parameter

DECLARE @Id INT
SELECT @Id = 69

;WITH Hierarchy (Id, ParentId, Data, Depth)
AS
( SELECT Id, ParentId, NULL AS Data, 0 AS Depth
  FROM Test
  UNION ALL
  SELECT h.Id, t.ParentId, COALESCE(h.Data, t.Data), Depth + 1 AS Depth
  FROM Hierarchy h
       INNER JOIN Test t ON t.Id = h.ParentId
)
SELECT *
FROM Hierarchy
WHERE Id = @Id

In case of a table with 50,000 rows the query with the constant runs for 10 milliseconds and the one with the parameter runs for 30 seconds (3,000 times slower).

It is not an option to move the last WHERE clause to the anchor definition of the recursion, as I would like to use the query to create a view (without the last WHERE). The select from the view would have the WHERE clause (WHERE Id = @Id) - I need this because of Entity Framework, but that is another story.

Can anybody suggest a way to force query #2 (with the parameter) to use the same query plan as query #1 (with the constant)?

I already tried playing with indexes but that did not help.

If somebody would like I can post the table definition and some sample data as well. I am using SQL 2008 R2.

Thank you for your help in advance!

Execution plan - Query #1 - with constant

Execution plan - Query #2 - with parameter

Answer 1

As Martin suggested in a comment under the question, the problem is that SQL server does not push down properly the predicate from the WHERE clause - see the link in his comment.

I ended up with creating a user defined table-valued function and use it with the CROSS APPLY operator for creating the view.

Let's see the solution itself.

User Defined Table-valued Function

CREATE FUNCTION [dbo].[TestFunction] (@Id INT)
RETURNS TABLE 
AS
RETURN 
(
    WITH
    Hierarchy (Id,  ParentId, Data, Depth)
    AS(
    SELECT Id, ParentId, NULL AS Data, 0 AS Depth FROM Test Where Id = @Id
    UNION ALL
    SELECT h.Id, t.ParentId, COALESCE(h.Data, t.Data), Depth + 1 AS Depth
        FROM Hierarchy h
            INNER JOIN Test t ON t.Id = h.ParentId
    )
    SELECT * FROM Hierarchy
)

View

CREATE VIEW [dbo].[TestView]
AS
SELECT t.Id, t.ParentId, f.Data, f.Depth
FROM
    Test AS t
    CROSS APPLY TestFunction(Id) as f

Query with constant

SELECT * FROM TestView WHERE Id = 69

Query with parameter

DECLARE @Id INT
SELECT @Id = 69
SELECT * FROM TestView WHERE Id = @Id

The query with the parmater executes basically as fast as the query with the constant.

Thank You Martin and for the others as well!

Answer 2

For your second Query try using the OPTIMIZE FOR or OPTION(RECOMPILE) query hint to see if that forces it to recomplile based on the provided parameter value.