带有选项的无限循环CTE(最大递归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(最大递归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)
编辑:易于理解的代码
已解决:因此,问题不是出自 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(最大递归0)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!