如何修复Azure Data Factory中递归CTE的‘最大递归100已耗尽’错误？

问题背景 #

尝试运行递归CTE查询并将结果集加载到Azure SQL数据库表时，ADF管道持续报错，已调整超时时间但无效。

原查询代码 #

WITH RecursiveCTE AS (
    SELECT  PARENT,CHILD , 1 AS Level
    FROM P_C

    UNION ALL

    SELECT  t.PARENT, t.CHILD , c.Level + 1
    FROM   P_C t
    INNER JOIN RecursiveCTE c ON t.PARENT = c.CHILD 
)
SELECT distinct PARENT,CHILD ,Level FROM RecursiveCTE;

错误详情 #

Azure Data Factory: Failure happened on 'Source' side. ErrorCode=SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed with the following error: 'The statement terminated. The maximum recursion 100 has been exhausted before statement completion.',Source=,''Type=System.Data.SqlClient.SqlException,Message=The statement terminated. The maximum recursion 100 has been exhausted before statement completion.,Source=.Net SqlClient Data Provider,SqlErrorNumber=530,Class=16,ErrorCode=-2146232060,State=1,Errors=[{Class=16,Number=530,State=1,Message=The statement terminated. The maximum recursion 100 has been exhausted before statement completion.,},],'

错误原因 #

错误代码530明确说明：递归CTE超出了SQL Server默认的最大递归深度（100层）。超时调整无法解决该问题，核心要么是递归层数确实超过默认限制，要么是数据存在循环引用导致递归无法终止。

修复方案 #

1. 调整递归深度上限 #

在查询末尾添加OPTION (MAXRECURSION n)参数，n为允许的最大递归层数（设为0表示无限制，生产环境不推荐），先验证是否是单纯层数不足的问题：

WITH RecursiveCTE AS (
    SELECT  PARENT,CHILD , 1 AS Level
    FROM P_C

    UNION ALL

    SELECT  t.PARENT, t.CHILD , c.Level + 1
    FROM   P_C t
    INNER JOIN RecursiveCTE c ON t.PARENT = c.CHILD 
)
SELECT distinct PARENT,CHILD ,Level FROM RecursiveCTE
OPTION (MAXRECURSION 1000); -- 可根据实际需求设置层数，或用0关闭限制

注意：生产环境禁用递归限制（设为0）需谨慎，若数据存在循环会导致无限递归消耗资源。

2. 检测并清理循环引用 #

若调整层数后仍报错，说明P_C表存在循环依赖（如A→B、B→A），导致递归无法终止。用以下查询定位循环记录：

WITH RecursiveCTE AS (
    SELECT 
        PARENT, 
        CHILD, 
        1 AS Level,
        CAST(PARENT AS VARCHAR(MAX)) + '→' + CAST(CHILD AS VARCHAR(MAX)) AS Path
    FROM P_C

    UNION ALL

    SELECT 
        t.PARENT, 
        t.CHILD, 
        c.Level + 1,
        c.Path + '→' + CAST(t.CHILD AS VARCHAR(MAX)) AS Path
    FROM   P_C t
    INNER JOIN RecursiveCTE c ON t.PARENT = c.CHILD 
    WHERE CHARINDEX(CAST(t.CHILD AS VARCHAR(MAX)), c.Path) = 0 -- 避免重复遍历已知路径
)
SELECT * FROM RecursiveCTE
WHERE CHARINDEX(CAST(CHILD AS VARCHAR(MAX)), Path) > 0; -- 筛选出存在循环的记录

找到循环记录后，需修正数据（如删除错误关联、调整层级关系）。

3. 优化递归逻辑 #

原查询中的DISTINCT会增加计算开销，且递归可能存在重复遍历。可考虑：

• 在递归CTE内部添加条件避免重复处理同一节点
• 限定递归起始范围，只遍历需要的分支，减少不必要的递归层数

内容的提问来源于stack exchange，提问作者Osceria