I am using a recursive CTE on a tree structure to list all the descendants of a particular node in the tree. If I write a literal node value in my WHERE
clause, SQL Server seems to actually apply the CTE just to that value, giving a query plan with low actual rows counts, et cetera:
However, if I pass the value as a parameter, it seems to realize (spool) the CTE and then filter it after the fact:
I could be reading the plans wrong. I haven’t noticed a performance issue, but I am worried that realization of the CTE could cause issues with larger data sets, especially in a busier system. Also, I normally compound this traversal on itself: I traverse up to ancestors and back down to descendants (to ensure that I gather all related nodes). Due to how my data is, each set of “related” nodes is rather small, so realization of the CTE doesn’t make sense. And when SQL Server seems to realize the CTE, it is giving me some quite large numbers in its “actual” counts.
Is there a way to get the parameterized version of the query to act like the literal version? I want to put the CTE in a reusable view.
Query with literal:
CREATE PROCEDURE #c AS BEGIN;
WITH descendants AS (SELECT
t.ParentId Id
,t.Id DescendantId
FROM #tree t
WHERE t.ParentId IS NOT NULL
UNION ALL SELECT
d.Id
,t.Id DescendantId
FROM descendants d
JOIN #tree t ON d.DescendantId = t.ParentId)
SELECT d.*
FROM descendants d
WHERE d.Id = 24
ORDER BY d.Id, d.DescendantId;
END;
GO
EXEC #c;
Query with parameter:
CREATE PROCEDURE #c (@Id BIGINT) AS BEGIN;
WITH descendants AS (SELECT
t.ParentId Id
,t.Id DescendantId
FROM #tree t
WHERE t.ParentId IS NOT NULL
UNION ALL SELECT
d.Id
,t.Id DescendantId
FROM descendants d
JOIN #tree t ON d.DescendantId = t.ParentId)
SELECT d.*
FROM descendants d
WHERE d.Id = @Id
ORDER BY d.Id, d.DescendantId;
END;
GO
EXEC #c 24;
Setup code:
DECLARE @count BIGINT = 100000;
CREATE TABLE #tree (
Id BIGINT NOT NULL PRIMARY KEY
,ParentId BIGINT
);
CREATE INDEX tree_23lk4j23lk4j ON #tree (ParentId);
WITH number AS (SELECT
CAST(1 AS BIGINT) Value
UNION ALL SELECT
n.Value * 2 + 1
FROM number n
WHERE n.Value * 2 + 1 <= @count
UNION ALL SELECT
n.Value * 2
FROM number n
WHERE n.Value * 2 <= @count)
INSERT #tree (Id, ParentId)
SELECT n.Value, CASE WHEN n.Value % 3 = 0 THEN n.Value / 4 END
FROM number n;