How can I avoid an eager spool with a recursive CTE query?

Question

I have a view that uses a recursive CTE to give me the list of descendants for any given record (SQL Server 2014):

CREATE VIEW descendants AS
    WITH results AS (
        SELECT  tbl.id,
                tbl.id AS ancestorId
        FROM    tbl
        UNION ALL
        SELECT  tbl.id,
                d.ancestorId
        FROM    tbl
                INNER JOIN results AS d ON d.id = tbl.parentId
    )
    SELECT * FROM results;

This works beautifully. A query like this returns the matching record ID and all descendant record IDs very quickly:

SELECT * FROM descendants WHERE ancestorId = 22;

However, once I use a variable, execution time goes from 0.1 seconds to 10 seconds!

DECLARE @p int;
SET @p = 22;
SELECT * FROM descendants WHERE ancestorId = @p;

The plans are very different.

The fast result, using a literal value:

The slow result, using a variable with the same value:

The source of evilness appears to be the eager spool.

How can I avoid this? I can't think of many use cases where this would be an advantage to the query.

Edit: I've seen some similar questions, but they seem to focus on the Halloween problem, which is not in play here. Since it's a comment, not an answer, I'll mention that the OPTION(RECOMPILE) suggestion works great and I'm looking into changing the "anchor" (I've forgotten this terminology since I first used a recursive CTE a long time ago and I need to go refresh my memory).

Try adding `OPTION (RECOMPILE)` - maybe a predicate pushing issue that applies to variables/params but not literals (i.e. similar to [this](https://sqlperformance.com/2013/03/t-sql-queries/the-problem-with-window-functions-and-views)) — Martin Smith, Apr 23 '19 at 17:08
In the recursive CTE, shouldn't you be joining `results` instead of `descendants`? Just a thought. — The Impaler, Apr 23 '19 at 17:10
How many rows are in the tables and are statistics maintained? If you are testing performance then I would follow the advice from @Martin Smith and force a recompile and also update the statistics. — Ross Bush, Apr 23 '19 at 17:11
I've repro-ed that filter at the root of the plan on SQL Server 2014 but not 2016 or later. Looks like both `OPTION (QUERYTRACEON 4199)` and `OPTION (RECOMPILE)` get rid of it — Martin Smith, Apr 23 '19 at 17:20
The problem is not the spool. It is that the predicate isn't pushed into the anchor member. In the fast plan it has a seek on `[tbl].[id]=(22)` and just does the recursive part for the results of that. In the slow plan it does a full scan in the anchor member and the recursive part for all rows in the table then filters out the unneeded ones at the end — Martin Smith, Apr 23 '19 at 17:22
Please see [paste the plan](https://www.brentozar.com/pastetheplan/instructions/) for a better way to include an execution plan in your question. — HABO, Apr 23 '19 at 17:27
Have you tried changing the variable to the anchor part of your recursive query? — Luis Cazares, Apr 23 '19 at 17:29
@TheImpaler, you're correct, I messed up when I replaced the actual query tables/fields with example values. Edited. — richardtallent, Apr 23 '19 at 18:32

How can I avoid an eager spool with a recursive CTE query?

0 Answers0