CURSOR优化替代方案：基于集合的SQL批量插入实现

我有一段使用CURSOR遍历INNER JOIN结果集的SQL查询，数据量较大时性能极差，急需优化。尝试寻找不使用CURSOR的基于集合的解决方案但未果。

需求说明 #

针对内连接结果的每一行，需向目标表插入至少2条数据，满足特定条件时额外插入第3条数据，具体规则如下：

• 必插行1：(CS, CE) = (E.CS, E.CE)
• 必插行2：(CS, CE) = (MAX(E.CS, W.CS), MAX(E.CE, W.CE))
• 条件插行3：若W.CS > E.CE，则插入(CS, CE) = (W.CS, W.CE)，否则不插入

原CURSOR实现代码 #

DECLARE 
    @W_CS NUMERIC,
    @W_CE NUMERIC,

    @E_CS NUMERIC,
    @E_CE NUMERIC,

    @CS_1 NUMERIC,
    @CE_1 NUMERIC,

    @CS_2 NUMERIC,
    @CE_2 NUMERIC,

    @maxLoopCount NUMERIC = 5000
-- 限制CURSOR最多循环5000次，无论内连接结果集多大

DECLARE Cursor_1 CURSOR FOR
SELECT  E.PID,
        E.EDATE,
        E.DAY,
        E.OpID,
        E.CS, 
        E.CE,
        W.CS, 
        W.CE,   
FROM    @Cat E
INNER JOIN @Weekly W
        ON  E.PID = W.PID
        AND E.EDATE = W.EDate
        AND E.DAY = W.DAY   
        AND E.OpID = W.OpID

OPEN    Cursor_1;

---- 首次读取游标数据
FETCH NEXT FROM Cursor_1    INTO 
        @E_CS,
        @E_CE,
        @W_CS,
        @W_CE;  

DECLARE @insertRowCount INT = 0


WHILE (@@FETCH_STATUS = 0  AND @maxLoopCount > 0)
BEGIN           
    -- 控制循环次数防止死锁
    Set @maxLoopCount = @maxLoopCount - 1


    SET         @insertRowCount = 0

    INSERT INTO @RETTB  
    SELECT      @E_CS,  @E_CE



    IF (@W_CS < @E_CE)                                      
    BEGIN
        SELECT  @insertRowCount = 2
                ,@CS_1  = @mW_StartCount
                ,@CE_1  = @mE_StartCount
                ,@CS_2  = @mE_EndCount
                ,@CE_2  = @mW_EndCount
    END
    ELSE
    BEGIN
        SELECT  @insertRowCount = 1
                ,@CS_1    = GREATEST(E_CS, W_CS) 
                ,@CE_1  =  GREATEST(E_CE, W_CE)
    END 

    IF @insertRowCount >= 1
    BEGIN
        INSERT INTO @RETTB
        SELECT @CS_1, @CE_1
    END

    IF @insertRowCount = 2
    BEGIN
        INSERT INTO @RETTB
        SELECT      @CS_2, @CE_2
    END

    ---- 读取下一行数据
    FETCH NEXT FROM Cursor_1    INTO 
        @E_CS,
        @E_CE,
        @W_CS,
        @W_CE;  

END

CLOSE   Cursor_1;
DEALLOCATE Cursor_1;

基于集合的优化方案 #

利用UNION ALL将所有需要插入的行一次性生成，避免游标逐行循环的性能损耗。如果需要保留原代码中最多处理5000行的限制，只需在主查询中添加对应逻辑即可。

优化后的SQL代码：

INSERT INTO @RETTB (CS, CE)
SELECT 
    CS, CE
FROM (
    -- 必插行1：(E.CS, E.CE)
    SELECT 
        E.CS AS CS,
        E.CE AS CE
    FROM @Cat E
    INNER JOIN @Weekly W
        ON E.PID = W.PID
        AND E.EDATE = W.EDate
        AND E.DAY = W.DAY
        AND E.OpID = W.OpID

    UNION ALL

    -- 必插行2：(MAX(E.CS, W.CS), MAX(E.CE, W.CE))
    SELECT 
        GREATEST(E.CS, W.CS) AS CS,
        GREATEST(E.CE, W.CE) AS CE
    FROM @Cat E
    INNER JOIN @Weekly W
        ON E.PID = W.PID
        AND E.EDATE = W.EDate
        AND E.DAY = W.DAY
        AND E.OpID = W.OpID

    UNION ALL

    -- 条件插行3：当W.CS > E.CE时插入(W.CS, W.CE)
    SELECT 
        W.CS AS CS,
        W.CE AS CE
    FROM @Cat E
    INNER JOIN @Weekly W
        ON E.PID = W.PID
        AND E.EDATE = W.EDate
        AND E.DAY = W.DAY
        AND E.OpID = W.OpID
        AND W.CS > E.CE
) AS AllInsertRows
-- 若需要保留原代码最多处理5000条原内连接记录，取消下面注释
-- WHERE EXISTS (
--     SELECT 1 
--     FROM (SELECT DISTINCT PID, EDATE, DAY, OpID FROM @Cat E INNER JOIN @Weekly W ON E.PID=W.PID AND E.EDATE=W.EDate AND E.DAY=W.DAY AND E.OpID=W.OpID) AS UniqueRows
--     ORDER BY PID, EDATE, DAY, OpID
--     OFFSET 0 ROWS FETCH NEXT 5000 ROWS ONLY
-- )

优化说明

1. 用UNION ALL替代游标循环，一次性生成所有符合规则的插入行，数据库可针对集合操作进行批量优化，大幅提升性能。
2. 每个分支对应需求中的一条插入规则，逻辑清晰且易于维护。
3. 若需限制最多处理5000条原内连接记录，可通过筛选唯一关联键（PID、EDATE、DAY、OpID）的前5000组来实现，避免重复计数插入行。

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