当 CTE 超过 maxrecursion 时我该怎么办？
我有一个 CTE 正在执行一些递归操作。 想象问题空间的最简单方法是世界各地的汽车中使用的电阻器，并且您拥有所有这些信息。 电阻器 A 用于板 B 和 C，板 B 和 C 用于 DashAssembly D、E、F...ZZ，用于汽车 AAA、AAB 和 AAC。
我让 CTE 处理一个零件，检查结果，一切正常（使用 MAXRECURSION 为 100）。 然后我添加在零件树中更常见的零件。 轰隆隆，破了。 我尝试将 MAXRECURSION 提高到 32767，但它仍然崩溃。 从数据来看，这个特定的部分到处都有使用。 在第二级递归中，它有 426 个父级。 等等，我认为是 6 级递归。 （我认为）这是有道理的，这是对 MAXRECURSION 的轰炸。
下一个问题是，B 计划是什么？ 我从来没有做过任何不使用 CTE 的递归。
更新：答案是：确保您没有任何循环引用，并确保您的递归表正确过滤其连接（我没有正确执行第二个）。 基本上我搞砸了，认为我的疯狂数据才是问题所在，但实际上是我蹩脚的 sql 技能。
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
大多数情况下，如果您达到了递归限制，则将限制提高到 $BIGNUM 并且仍然达到它，这意味着您有一个循环引用。
您应该检查以确保没有任何循环引用并且您的递归表正在正确过滤。
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.