百万级递归关联文档溯源查询优化方案咨询

针对你遇到的百万级递归文档追溯性能拉胯的问题，我强烈推荐用CTE递归查询替代原来的While循环存储过程——数据库引擎对CTE的递归逻辑会做更智能的执行计划优化，尤其适合处理大规模数据集。

先明确你的数据结构与需求 #

你给出的原始数据结构：

DocumentType | CodDocument | DocumentTypeOrigin | CodDocumentOrigin
------------|------------|-------------------|-------------------
O           | 12345      | E                 | 32456
E           | 32456      | P                 | 98472
P           | 98472      | A                 | 29503
A           | 29503      | NULL              | NULL

核心需求是：给每一条文档记录补充最终追溯到的A类型源文档信息，输出包含原始字段+FinalDocumentTypeOrigin、FinalCodDocumentOrigin。

优化后的CTE递归查询实现 #

下面是针对SQL Server的落地代码，逻辑是从每个节点向上递归追溯，直到找到DocumentType = 'A'的根节点，同时保留每条记录的原始信息：

WITH DocumentRecursion AS (
    -- 锚点成员：先定位所有A类型的根节点，它们的最终源就是自身
    SELECT 
        DocumentType,
        CodDocument,
        DocumentTypeOrigin,
        CodDocumentOrigin,
        DocumentType AS FinalDocumentTypeOrigin,
        CodDocument AS FinalCodDocumentOrigin
    FROM YourDocumentTable
    WHERE DocumentType = 'A'

    UNION ALL

    -- 递归成员：向上关联父节点，直接继承父节点的最终源信息
    SELECT 
        dt.DocumentType,
        dt.CodDocument,
        dt.DocumentTypeOrigin,
        dt.CodDocumentOrigin,
        dr.FinalDocumentTypeOrigin,
        dr.FinalCodDocumentOrigin
    FROM YourDocumentTable dt
    INNER JOIN DocumentRecursion dr 
        ON dt.DocumentTypeOrigin = dr.DocumentType 
        AND dt.CodDocumentOrigin = dr.CodDocument
    WHERE dt.DocumentType != 'A' -- 跳过已处理的根节点
)
-- 输出所有记录，按类型排序方便查看层级
SELECT * FROM DocumentRecursion
ORDER BY DocumentType DESC;

性能优化的关键要点 #

• 索引必须跟上：给关联字段创建复合索引(DocumentTypeOrigin, CodDocumentOrigin)，同时包含DocumentType, CodDocument——这能直接避免递归时的全表扫描，是百万级数据性能提升的核心。
• 减少递归冗余：锚点直接定位所有根节点，递归只处理非根节点，避免不必要的循环次数。
• 处理深层级场景：如果你的文档层级超过默认的100层，可以在查询末尾加上OPTION (MAXRECURSION 0)（0表示无限制），但要先确保数据没有循环引用（比如A指向B，B又指向A），否则会陷入无限递归。
• 对比执行计划：可以把原来的While循环SP和这个CTE的执行计划对比一下，你会发现CTE被优化成了更高效的迭代器，而循环SP大概率是多次表扫描或游标操作，这就是性能差的根源。

测试输出结果 #

用你给出的示例数据执行上述查询，会得到你期望的输出：

DocumentType | CodDocument | DocumentTypeOrigin | CodDocumentOrigin | FinalDocumentTypeOrigin | FinalCodDocumentOrigin
------------|------------|-------------------|-------------------|------------------------|------------------------
O           | 12345      | E                 | 32456             | A                      | 29503
E           | 32456      | P                 | 98472             | A                      | 29503
P           | 98472      | A                 | 29503             | A                      | 29503
A           | 29503      | NULL              | NULL              | A                      | 29503

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