带有选项的无限循环 CTE (maxrecursion 0)
I have CTE query with large record on it. Previously it worked fine. But lately, it throws an error for some members
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.
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?
Here's my code:
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)
EDIT: remove unnecessary code to easy understand
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)
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 );
相关文章