What should I do when I go over maxrecursion with a CTE?

Question

I have a CTE that's doing some recursion. The easiest way to imagine the problem space is a resistor that is used in cars all over the world and you have all that information. Resistor A is used in Board B and C which is used in DashAssembly D,E,F...ZZ which is used in Car AAA, AAB and AAC.

I got the CTE working with one part, checked the results, everything's fine (using a MAXRECURSION of 100). Then I add in a part that's a little more common in the part trees. Boom, broken. I tried bumping up the MAXRECURSION to 32767 and it still broke. Looking at the data, this particular part is used all over the place. At the second level of recursion it has 426 parents. And so on, 6 levels of recursion I think. It makes sense (I think) that this is bombing out MAXRECURSION.

Next question is, what's plan B? I've never done any recursion that didn't use CTEs.

Update: the answer is: make sure you don't have any circular references and make sure that your recursion table is filtering its join properly (I didn't do the second one properly). Basically I goofed up and thought that my it was my crazy wild data that was the issue when instead it was my crappy sql skills.

Answer 1

Most often, if you're hitting a recursion limit, you bump the limit to $BIGNUM and you're still hitting it, that means you have a circular reference.

You should check to make sure that you don't have any circular references and that your recursion table is filtering properly.