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