Recursive CTE causes slowness and index scan

Question

I have a table (Location_Tree) containing location information, arranged in a Id/ParentId structure on several levels, from level 0 of "World" right down to individual cities, via worldregions, countries, states, counties, cantons and cities. We have another table of incidents which has a column "CreatedWithLocationId" which indicates the location for the incident, but there is nothing to say whether the location provided is a city, county, country or whatever - in other words the accuracy is undefined.

I am using a query to make a report that takes the location of one incident and shows other incidents that are listed "nearby". the best matches will be those that share a location with the input location, but if not enough matches are found, I am "zooming out" of the location, to search the parent locations up the tree until I find enough matches for the report requirements.

In order to achieve this, I have made a recursive CTE, which will get all the children of a given location from the Location_Tree table. So I take my input location, find the parentId and then find all the locations that share that parentId, or have it as grandparents and so on, limiting by level and so on as required.

DECLARE @Ancestor_Id INT = 1;

WITH Location_Children AS
(
SELECT @Ancestor_Id AS Id, NULL AS ParentId
UNION ALL
 SELECT
    B.Id, B.ParentId
 FROM       Location_Tree       AS B
 INNER JOIN Location_Children   AS C    ON B.ParentId = C.Id
)

SELECT * FROM Location_Children;

Even if the only row that is returned is the @Ancestor_Id AS Id, NULL AS ParentId row, the above query always causes a clustered index scan on the primary key of the Location_Tree table () and an eager spool - All the vast number of rows are involved in the execution plan and the query takes about 15 seconds to complete, returning my one row.

--> Execution Plan

Does anyone have any suggestions about how I might go about speeding things up a bit? I have tried adding indexes and so on, and I'm a bit reluctant to do a massive query to use CASE statements or a series of left joins to replace the CTE, as it will take a lot of scripting... I have tried every permutation of this query, using inline functions, custom table data types, (almost) everything and to no avail... What should I try next?

Answer 1

If the Eager Spool is consuming that much of the query cost, then it may be instructive to look at Itzik Ben-Gan's post on Divide and Conquer Halloween: Avoid the Overhead of Halloween Protection. The Halloween Problem occurs in situations like UPDATE statements involving nonclustered indexes, where the query optimizer may refer to index values that are out of synch with the updated values, leading to incorrect data or even infinite loops. The Eager Spool is a blocking operator used by the SQL Server query optimizer in situations like these as Halloween Protection. In his excellent four-part series (which Ben-Gan also links to) on the Halloween Problem, Paul White explains the need for Eager Spools:

"There is no hint or trace flag to prevent inclusion of the spool in this execution plan because it is required for correctness. As its name suggests, the spool eagerly consumes all rows from its child operator (the Index Seek) before returning a row to its parent Compute Scalar. The effect of this is to introduce complete phase separation – all qualifying rows are read and saved into temporary storage before any updates are performed."

They're also sometimes needed in recursive CTEs, which update internal work tables (in TempDB, if I remember correctly). Most sources say that you're basically stuck with them; I have been able to remove costly Eager Spools by thoroughly indexing all of the columns involved in joins, but the resulting execution plans haven't been any faster to date and exhibit almost byzantine complexity; the performance hit was merely transferred to equally costly Concatenate operators. Some sources suggest in an off-hand way that WITH (NOLOCK) or WITH (READ UNCOMMITTED) might help, but I haven't had any luck with these; they result in the exact same execution plans every time, with the costly Eager Spools still in place. I don't want to plagiarize Ben-Gan's code, especially since I haven't tried it yet, but his workaround involving ditching the recursive CTE approach altogether and doing recursive updates on a pair of temp tables or table variables, in order to fool the optimizer into removing the Halloween Protection mechanism. I ran into this issue just a few days ago on a pair of critical long-running queries and never did succeed in removing the Eager Spools; I was only able to get them to run by ruthlessly weeding out unnecessary duplicates from the queries and other such optimizations, all of which required hours of experimentation and the addition of many complicated CASE statements to the joins of the recursive CTEs to cut down on the number of unnecessary rows being processed. The queries I was working on were generative recursive CTEs that created consecutive permutations of data; at certain points I was able to reduce the overall computational costs by precalculating the numbers that identified the permutations, but this may not work in your situation, which is a parent-child retrieval CTE.

I wish I could be more encouraging, but one way or another you'll probably be stuck with an expensive blocking Eager Spool, unless you use a drastic workaround like Ben-Gan's that ditches the recursive CTE approach altogether. If you want to keep a recursive CTE approach, your only choice may be to provide complex logic that reduces the number of unnecessary rows being processed. Slapping nonclustered indexes on the join conditions might also provide a little benefit, but basically you're looking at a series of piecemeal optimizations that may be time-consuming to write.