带选项的无限循环 CTE (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 );
相关文章