Is it possible to force SQL Server to use the plan I want to optimise recursive cte query

Question

I have a query in a view using a recursive cte on a large tree that works well when queried with a hardcoded number but not with a parameter. Is it possible to force SQL Server to use the plan I want to optimise this recursive cte query? Any ideas would be appreciated.

Here is the view with the recursive CTE - it retrieves all nodes under the given entity:

CREATE VIEW adams_test_view AS
WITH eq_mi_cte(miId, eqId, miName, miCode) AS
    (SELECT ent.id, ent.id, ent.name, ent.code
    FROM entity ent
    UNION ALL
    SELECT e.id, eq_mi_cte.eqid, e.name, e.code
    FROM entity e
    INNER JOIN eq_mi_cte ON e.pid = eq_mi_cte.miid)
SELECT * FROM eq_mi_cte

The query on the view with a parameter seems to query the entire view then filter it which never finishes as the tree is too large - we get a maximum recursion error:

DECLARE @TopLevelEnt int
SET @TopLevelEnt = 187317;
select * from adams_test_view
WHERE eqId = @TopLevelEnt

Here is the plan (my appologies for the index names):

  |--Filter(WHERE:([Recr1009]=[@TopLevelEnt]))
       |--Index Spool(WITH STACK)
            |--Concatenation
                 |--Compute Scalar(DEFINE:([Expr1012]=(0)))
                 |    |--Index Scan(OBJECT:([local_dbname].[dbo].[Entity].[EntityParentId] AS [ent]))
                 |--Assert(WHERE:(CASE WHEN [Expr1014]>(100) THEN (0) ELSE NULL END))
                      |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1014], [Recr1004], [Recr1005], [Recr1006], [Recr1007]))
                           |--Compute Scalar(DEFINE:([Expr1014]=[Expr1013]+(1)))
                           |    |--Table Spool(WITH STACK)
                           |--Index Seek(OBJECT:([local_dbname].[dbo].[Entity].[EntityParentId] AS [e]), SEEK:([e].[PId]=[Recr1004]) ORDERED FORWARD)

Now when I do the same query using a hardcoded value it returns fine:

query:

SELECT * FROM adams_test_view
WHERE eqId = 187317

plan:

  |--Index Spool(WITH STACK)
       |--Concatenation
            |--Compute Scalar(DEFINE:([Expr1012]=(0)))
            |    |--Clustered Index Seek(OBJECT:([local_dbname].[dbo].[Entity].[PK__Entity__2E1BDC42] AS [ent]), SEEK:([ent].[Id]=(187317)) ORDERED FORWARD)
            |--Assert(WHERE:(CASE WHEN [Expr1014]>(100) THEN (0) ELSE NULL END))
                 |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1014], [Recr1004], [Recr1005], [Recr1006], [Recr1007]))
                      |--Compute Scalar(DEFINE:([Expr1014]=[Expr1013]+(1)))
                      |    |--Table Spool(WITH STACK)
                      |--Index Seek(OBJECT:([local_dbname].[dbo].[Entity].[EntityParentId] AS [e]), SEEK:([e].[PId]=[Recr1004]) ORDERED FORWARD)

I tried doing a reorganise of the entity pk, and running sp_updatestats but made no difference.

Also tried adding an optimise for hint but does not seem to be picking it up eg.

DECLARE @TopLevelEnt int
SET @TopLevelEnt = 187317;
select * from adams_test_view
WHERE eqId = @TopLevelEnt
OPTION (OPTIMIZE FOR (@TopLevelEnt = 187317))

I'm running this on a SQL Server 2005 Express database from SQL Server Management Studio Express 2008 R2

Any hints or hackery would be appreciated.

Answer 1

What you see is normal because the view first return every thing and then look at the variable and filter accordingly. When it is hard coded, it choose another plan because it knows from the begining which filter to apply on it.

Have you tried doing the same thing with an inline table value function with a filter on the upper select of the cte?