优化递归CTE实现序数链首行检测及替代方案咨询

数据示例 #

需求 #

给定UserId和SubId，查找同一EduId序数链中的最小SemOrd。例如，当UserId=1、SubId=706时，结果应为SemOrd=12。

初始方案 #

采用递归CTE实现，代码如下：

;WITH Base AS (
    SELECT
        UserId,
        SubId,
        EduId,
        SemOrd,
        ROW_NUMBER() OVER (PARTITION BY UserId ORDER BY SemOrd ASC) AS Ordinal,
        ROW_NUMBER() OVER (PARTITION BY UserId, EduId ORDER BY SemOrd ASC) AS OrdinalParted
    FROM /* ... */
), Chain AS (
    SELECT
        SemOrd AS SemOrd_First,
        UserId,
        SubId,
        EduId,
        SemOrd,
        Ordinal,
        OrdinalParted
    FROM Base
    UNION ALL
    SELECT
        Chain.SemOrd_First,
        Base.UserId,
        Base.SubId,
        Base.EduId,
        Base.SemOrd,
        Base.Ordinal,
        Base.OrdinalParted
    FROM
        Base
        INNER JOIN Chain
            ON Base.UserId = Chain.UserId
            AND Base.EduId = Chain.EduId
            AND Base.Ordinal = Chain.Ordinal + 1
            AND Base.OrdinalParted = Chain.OrdinalParted + 1
), Minimal AS (
    SELECT
        UserId,
        SubId,
        MIN(SemOrd_First) AS SemOrd_First
    FROM Chain
    GROUP BY
        UserId,
        SubId
)
SELECT SemOrd_First FROM Minimal WHERE UserId = 1 AND SubId = 706

现有问题 #

该方案在小表中可行，但在生产大表中因需先计算所有CTE条目再执行WHERE过滤，性能极差。若将WHERE子句移至首个CTE中：

;WITH Base AS (
    SELECT
        UserId,
        SubId,
        EduId,
        SemOrd,
        ROW_NUMBER() OVER (PARTITION BY UserId ORDER BY SemOrd ASC) AS Ordinal,
        ROW_NUMBER() OVER (PARTITION BY UserId, EduId ORDER BY SemOrd ASC) AS OrdinalParted
    FROM /* ... */
    WHERE UserId = 1 AND SubId = 706
), Chain AS (
/* ... */

则查询速度快，但无法创建可动态指定UserId和SubId的视图，且SQL Server无法将WHERE条件上推至首个CTE以减少执行计划的工作量。

咨询问题 #

1. 是否有办法让SQL Server将WHERE子句上推？
2. 是否存在递归CTE的替代方案？
   若以上方案均不可行，则考虑使用存储过程。

问题解答 #

1. 让SQL Server上推WHERE子句的方法

SQL Server对包含窗口函数的CTE，默认无法直接将外层WHERE条件上推到CTE内部，但可以通过以下方式优化：

• 使用内联表值函数（ITVF）：将逻辑封装为带参数的内联表值函数，SQL Server能更好地对参数化查询做优化，自动将过滤条件下推到底层查询。示例：

CREATE FUNCTION dbo.GetMinSemOrd(@UserId INT, @SubId INT)
RETURNS TABLE
AS
RETURN (
    WITH Base AS (
        SELECT
            UserId,
            SubId,
            EduId,
            SemOrd,
            ROW_NUMBER() OVER (PARTITION BY UserId ORDER BY SemOrd ASC) AS Ordinal,
            ROW_NUMBER() OVER (PARTITION BY UserId, EduId ORDER BY SemOrd ASC) AS OrdinalParted
        FROM /* 你的表名 */
        WHERE UserId = @UserId AND SubId = @SubId
    ), Chain AS (
        SELECT
            SemOrd AS SemOrd_First,
            UserId,
            SubId,
            EduId,
            SemOrd,
            Ordinal,
            OrdinalParted
        FROM Base
        UNION ALL
        SELECT
            Chain.SemOrd_First,
            Base.UserId,
            Base.SubId,
            Base.EduId,
            Base.SemOrd,
            Base.Ordinal,
            Base.OrdinalParted
        FROM
            Base
            INNER JOIN Chain
                ON Base.UserId = Chain.UserId
                AND Base.EduId = Chain.EduId
                AND Base.Ordinal = Chain.Ordinal + 1
                AND Base.OrdinalParted = Chain.OrdinalParted + 1
    ), Minimal AS (
        SELECT
            UserId,
            SubId,
            MIN(SemOrd_First) AS SemOrd_First
        FROM Chain
        GROUP BY
            UserId,
            SubId
    )
    SELECT SemOrd_First FROM Minimal
);

调用时执行SELECT * FROM dbo.GetMinSemOrd(1,706)即可，优化器会自动减少不必要的数据扫描。

• 使用OPTION (RECOMPILE)提示：在查询末尾添加该提示，让SQL Server针对当前参数值重新生成执行计划，可能触发条件上推，但会增加编译开销，适合非高频查询场景。

2. 递归CTE的替代方案：间隙与孤岛算法

可以利用间隙与孤岛（Gaps and Islands） 逻辑替代递归，性能更优且无需递归操作：
核心思路是：同一EduId的连续序数链（孤岛）可以通过Ordinal - OrdinalParted的差值唯一标识，同一孤岛的该差值相同。只需找到目标记录所在孤岛，再取该孤岛的最小SemOrd即可。

示例代码：

DECLARE @UserId INT = 1, @SubId INT = 706;

WITH Base AS (
    SELECT
        UserId,
        SubId,
        EduId,
        SemOrd,
        -- 全局序号
        ROW_NUMBER() OVER (PARTITION BY UserId ORDER BY SemOrd ASC) AS Ordinal,
        -- 按EduId分组的序号
        ROW_NUMBER() OVER (PARTITION BY UserId, EduId ORDER BY SemOrd ASC) AS OrdinalParted,
        -- 计算孤岛标识：同一连续链的差值一致
        ROW_NUMBER() OVER (PARTITION BY UserId ORDER BY SemOrd ASC) 
        - ROW_NUMBER() OVER (PARTITION BY UserId, EduId ORDER BY SemOrd ASC) AS IslandId
    FROM /* 你的表名 */
), TargetInfo AS (
    -- 获取目标记录的EduId和所属孤岛标识
    SELECT EduId, IslandId
    FROM Base
    WHERE UserId = @UserId AND SubId = @SubId
)
-- 查找目标孤岛的最小SemOrd
SELECT MIN(b.SemOrd) AS SemOrd_First
FROM Base b
JOIN TargetInfo t ON b.UserId = @UserId AND b.EduId = t.EduId AND b.IslandId = t.IslandId;

该方法仅需两次窗口函数计算，性能远优于递归CTE，且可封装为带参数的函数或存储过程，支持动态传入参数。

3. 存储方案备选：存储过程

如果上述方法仍无法满足需求，存储过程是可靠选择。存储过程可直接将过滤条件写入底层查询，确保只扫描必要数据，同时支持动态参数：

CREATE PROCEDURE dbo.GetMinSemOrdProc
    @UserId INT,
    @SubId INT
AS
BEGIN
    SET NOCOUNT ON;
    WITH Base AS (
        SELECT
            UserId,
            SubId,
            EduId,
            SemOrd,
            ROW_NUMBER() OVER (PARTITION BY UserId ORDER BY SemOrd ASC) AS Ordinal,
            ROW_NUMBER() OVER (PARTITION BY UserId, EduId ORDER BY SemOrd ASC) AS OrdinalParted,
            ROW_NUMBER() OVER (PARTITION BY UserId ORDER BY SemOrd ASC) 
            - ROW_NUMBER() OVER (PARTITION BY UserId, EduId ORDER BY SemOrd ASC) AS IslandId
        FROM /* 你的表名 */
        WHERE UserId = @UserId
    ), TargetInfo AS (
        SELECT EduId, IslandId
        FROM Base
        WHERE SubId = @SubId
    )
    SELECT MIN(b.SemOrd) AS SemOrd_First
    FROM Base b
    JOIN TargetInfo t ON b.EduId = t.EduId AND b.IslandId = t.IslandId;
END;

调用时执行EXEC dbo.GetMinSemOrdProc 1,706即可。

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