sql-server - example - transact sql maxrecursion

Bucle infinito CTE con OPCIÓN(maxrecursion 0)

Question

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

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

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

Answer 1

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