Bucle infinito CTE con OPCIÓN (maxrecursion 0)

Tengo una consulta CTE con un gran registro en ella. Previamente funcionó bien. Pero últimamente, arroja un error para algunos miembros

La statement terminó. La recursión máxima 100 se ha agotado antes de completar la statement.

Así que puse OPTION (maxrecursion 0) u OPTION (maxrecursion 32767) en mi consulta, porque no quiero limitar los registros. Pero, el resultado es que la consulta tarda una eternidad en cargarse. ¿Cómo puedo solucionar esto?

Aquí está mi código:

 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) 

EDITAR: eliminar el código innecesario para facilitar su comprensión

SOLUCIONADO: Entonces, el problema no viene de maxrecursion . Es del CTE. No sé por qué, pero posiblemente contenga algún ciclo de patrocinio: A -> B -> C -> A -> … (Gracias a @HABO)

Intenté este método y funciona. Bucle infinito en CTE al analizar la tabla de autorreferencia

Si está llegando al límite de recursión, tiene una profundidad considerable en las relaciones de patrocinio o un bucle en los datos. Una consulta como la siguiente detectará bucles y terminará la recursión:

 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 );