Hi Chris,

I had to add a quick index on acct_no to get the table scale-up thing to work in my lifetime (the MAX gets out of hand without it!), but other than that:

This looks to be an interesting way to speed up this sort of recursive query, and it certainly seems to work. There are a number of similarities to the set-based iteration method, like the pre-computing of an order using a ranking function. I want to look at this a bit more deeply before saying much more, but the index spool at the 'lowest level' of the plan seems to be one of the keys to success here - it saves scanning the input table a good many times. I would expect an explicit index on the same keys as the spool to be beneficial, I'll come back to this.

You're right that recursive CTEs can be fast, but it is relative. I sometimes use them to seek down the distinct keys of a large index rather than scanning the whole thing and relying on an aggregate, for example. Where a good set-based alternative exists I usually go with that. Maybe I'll revisit that once I've had a deeper think on this.

I must admit I spent a few minutes expanding the data set to work with the set-based iteration method to see how that went. The recursive CTE ran for just over seven seconds on my old laptop before starting to return results. The set-based iteration method ran for two 😛

Paul