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