SQL CTE (Common Table Expression) recursion limit

Tenemos un sistema que muestra mensajes paginados de 20 en 20. Un mensaje puede ser respuesta de otro, hasta “n” niveles, formándose un árbol. Un usuario, por error, relacionó el mensaje “2” como respuesta del mensaje “1”, y, el mensaje “1” como respuesta del mensaje “2”, formando un círculo vicioso recursivo. Revisando desde el SQL Server Managemente Studio, hallé el siguiente mensaje de error:

Msg 530, Level 16, State 1, Procedure pa_SN_Mensaje_ListarPaginadoxSeccion, Line 23
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

El procedimiento almacenado en cuestión es algo así:

WITH ListaMensajes(NodeId, IDSubSeccion, Mensaje, Fecha, Autor , Level, Father, Tipo, Estado, RowID, Orden, Rsp, NroMensaje)
AS
(
SELECT M.IDMensaje as NodeId, M.IDSubseccion, M.Contenido as Mensaje,
M.Fecha as Fecha, M.EMISOR as Autor,
10 as Level, M.IDDominio as Father, M.TipoMensaje,
M.Estado as Estado, M.ROWID, M.IDMensaje as Orden,
dbo.fxText(isnull(M.FLAGRSP,'0')) as Rsp, M.NroMensaje
FROM
(
select * from some_table m
) M
WHERE M.ROWID >= @Desde and M.ROWID <= @Hasta
UNION ALL
SELECT N.IDMensaje as NodeId, N.IDSubseccion, N.Contenido as Mensaje,
N.Fecha as Fecha, N.EMISOR as Autor,
case
when N.TipoMensaje = @TypeSeguimiento then
102
else
101
end as Level, N.IDDominio as Father, N.TipoMensaje,
N.Estado as Estado, LST.ROWID, N.IDDominio as Orden, dbo.fxText('0') as Rsp, N.NroMensaje
FROM MENSAJE N INNER JOIN LISTAMENSAJES LST
ON (N.IDMENSAJE = LST.Father )
)

SELECT  *  FROM LISTAMENSAJES LST

Problema: Al usar un CTE recursivo, se pueden devolver como máximo 100 filas en el resultado. En mi caso, al ser un círculo vicioso, sobrepasaba las 100 filas ocasionando el error.

Solución:

En mi caso, desligar los mensajes, ya que eran un error del usuario, y validar que no se puedan relacionar mensajes recursivamente.

Temporalmente, se pede modificar la consulta SQL para evitar un “loop ” (bucle) infinito:

OPTION (MAXRECURSION 2)--Máximo nivel de recursividad: 2

Referencias:

http://msdn.microsoft.com/en-us/library/ms175972.aspx

An incorrectly composed recursive CTE may cause an infinite loop. For example, if the recursive member query definition returns the same values for both the parent and child columns, an infinite loop is created. To prevent an infinite loop, you can limit the number of recursion levels allowed for a particular statement by using the MAXRECURSION hint and a value between 0 and 32,767 in the OPTION clause of the INSERT, UPDATE, MERGE, DELETE, or SELECT statement. This lets you control the execution of the statement until you resolve the code problem that is creating the loop. The server-wide default is 100. When 0 is specified, no limit is applied. Only one MAXRECURSION value can be specified per statement. For more information, see Query Hints (Transact-SQL).

http://msdn.microsoft.com/en-us/library/ms186243.aspx

SQL Server: How to limit CTE recursion to rows just recursivly added?

http://stackoverflow.com/questions/634971/sql-server-how-to-limit-cte-recursion-to-rows-just-recursivly-added

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: