带选项的无限循环 CTE (maxrecursion 0) [英] Infinite loop CTE with OPTION (maxrecursion 0)

问题描述

我有大量记录的 CTE 查询.以前它工作得很好.但是最近，它为某些成员抛出错误

I have CTE query with large record on it. Previously it worked fine. But lately, it throws an error for some members

语句终止.最大递归100在语句完成前已用完.

The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

所以我将 OPTION (maxrecursion 0) 或 OPTION (maxrecursion 32767) 放在我的查询中，因为我不想限制记录.但是，结果是查询需要永远加载.我该如何解决这个问题?

So I put OPTION (maxrecursion 0) or OPTION (maxrecursion 32767) on my query, because I don't want to limit the records. But, the result is the query takes forever to load. How do I solve this?

这是我的代码:

with cte as(
-- Anchor member definition
    SELECT  e.SponsorMemberID , e.MemberID, 1 AS Level
    FROM tblMember AS e 
    where e.memberid = @MemberID

union all

-- Recursive member definition
    select child.SponsorMemberID , child.MemberID, Level + 1
    from tblMember child 

join cte parent

on parent.MemberID = child.SponsorMemberID
)
-- Select the CTE result
    Select distinct a.* 
    from cte a
    option (maxrecursion 0)

删除不必要的代码以易于理解

remove unnecessary code to easy understand

已解决:所以问题不是来自 maxrecursion.它来自 CTE.我不知道为什么，但它可能包含任何赞助周期:A -> B -> C -> A -> ...(感谢@HABO)

SOLVED: So the issue is not came from maxrecursion. It's from the CTE. I don't know why but possibly it contain any sponsor cycles: A -> B -> C -> A -> ... (Thanks to @HABO)

我试过这个方法，效果很好.解析自引用表时 CTE 中的无限循环

I tried this method and it works. Infinite loop in CTE when parsing self-referencing table

推荐答案

如果您达到了递归限制，那么您要么在赞助关系上有相当大的深度，要么在数据中有一个循环.像下面这样的查询将检测循环并终止递归:

If you are hitting the recursion limit, you either have considerable depth in sponsoring relationships or a loop in the data. A query like the following will detect loops and terminate the recursion:

declare @tblMember as Table ( MemberId Int, SponsorMemberId Int );
insert into @tblMember ( MemberId, SponsorMemberId ) values
  ( 1, 2 ), ( 2, 3 ), ( 3, 5 ), ( 4, 5 ), ( 5, 1 ), ( 3, 3 );
declare @MemberId as Int = 3;
declare @False as Bit = 0, @True as Bit = 1;

with Children as (
  select MemberId, SponsorMemberId,
    Convert( VarChar(4096), '>' + Convert( VarChar(10), MemberId ) + '>' ) as Path, @False as Loop
    from @tblMember
    where MemberId = @MemberId
  union all
  select Child.MemberId, Child.SponsorMemberId,
    Convert( VarChar(4096), Path + Convert( VarChar(10), Child.MemberId ) + '>' ),
    case when CharIndex( '>' + Convert( VarChar(10), Child.MemberId ) + '>', Path ) = 0 then @False else @True end
    from @tblMember as Child inner join
      Children as Parent on Parent.MemberId = Child.SponsorMemberId
    where Parent.Loop = 0 )
  select *
    from Children
    option ( MaxRecursion 0 );

这篇关于带选项的无限循环 CTE (maxrecursion 0)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！