SQL was never intended for this kind of stuff; I'm sure that Fabian Pascal will tell us all about it.
And indeed, recursive CTEs are awful, confusing, and severely limited ... but they look cool and smart, and smart people use them (I hate them).
In any case, I used a procedural approach to this problem and, with a few smart optimizations, managed to squeeze some really spectacular performances (757K tree records with 20K unique nodes in just 5 seconds without any indexes, so it probably can go even faster).
SQL was never intended for this kind of stuff; I'm sure that Fabian Pascal will tell us all about it.
And indeed, recursive CTEs are awful, confusing, and severely limited ... but they look cool and smart, and smart people use them (I hate them).
In any case, I used a procedural approach to this problem and, with a few smart optimizations, managed to squeeze some really spectacular performances (757K tree records with 20K unique nodes in just 5 seconds without any indexes, so it probably can go even faster).