10

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

Community
  • 1
  • 1
vantian
  • 848
  • 3
  • 10
  • 25
  • Are you sure the data doesn't contain any sponsor cycles: A -> B -> C -> A -> ...? Or self-sponsored members? – HABO Feb 26 '13 at 04:06
  • have you checked the query plan? Have you tried to eliminate each of your joins to first check the cte, then check the HigRank, gt, TotCust separately? – cha Feb 26 '13 at 04:10
  • HABO: no, the member on tblMember have an unique value cha: I think the query is working fine. it works if the member has a small records – vantian Feb 26 '13 at 04:20
  • 1
    cha: I think the query is working fine. it works if the member has a small records – vantian Feb 26 '13 at 04:26

1 Answers1

11

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 );
HABO
  • 15,314
  • 5
  • 39
  • 57
  • I run this query, is successful but result nothing. does it mean there's no looping on the CTE? – vantian Feb 26 '13 at 06:13
  • Hi HABO, I solve this issue. please check my update. thanks for the hint – vantian Feb 26 '13 at 06:47
  • @vantian - You didn't get any rows where `Loop` was `1`? Did you change both occurrences of `@tblMember` to `tblMember` in the query? Just curious since the answer you prefer uses a rather similar technique to track the path/levels to locate loops. – HABO Feb 26 '13 at 13:46
  • One more thing as you mentioned that there is no limit. so in case of CTE is having maxrecusrion is set to 0 is fine but the max limit of recursion in CTE is 32,767. Refer this http://www.sql-server-helper.com/error-messages/msg-310.aspx – Rahul Neekhra Jan 13 '17 at 12:45