如何移除需基于前序结果查询的CURSOR并优化行程时间计算？

我们有两张核心表：

• Sections：存储行程段数据，包含起点、终点、行程类型，每个类型对应一组时间窗口
• SectionTimeWindows：定义各行程类型在不同时间区间的运行时长，根据到达时间匹配对应窗口计算下一段的到达时间

需要基于起始出发时间生成StopTimes表，规则是：

1. StopTimes包含N+1条记录（N为行程段数量），第一条是首段起点的停靠记录
2. 当前行程段的到达时间 = 前序到达时间 + 匹配到的对应时间窗口的运行时长

目前采用CURSOR遍历实现，但百万级数据下耗时长达6小时。尝试过LAG、SUM OVER等函数，但因到达时间是递归计算结果无法直接关联查询，现咨询：

• 是否可以移除CURSOR？
• 若无法移除，如何优化每次FETCH中的SELECT TOP 1操作？

简化实现代码 #

CREATE TABLE #Sections
(
    SectionID INT PRIMARY KEY IDENTITY,
    SectionTypeID INT,
    StartPlaceID INT,
    EndPlaceID INT,
    [Order] INT,
);

CREATE TABLE #SectionTimeWindows
(
    SectionTimeWindowID INT PRIMARY KEY IDENTITY,
    SectionTypeID INT,
    WindowStart INT,
    WindowEnd INT,
    RunningTime INT,
)

CREATE TABLE #StopTimes
(
    StopTimeID INT PRIMARY KEY IDENTITY,
    PlaceID INT,
    IncommingSectionID INT NULL,
    ArrivalTime INT,
    [Order] INT,
)

INSERT INTO #Sections (SectionTypeID, StartPlaceID, EndPlaceID, [Order]) VALUES 
    (1, 10, 20, 1),
    (1, 20, 30, 2),
    (1, 30, 40, 3),
    (1, 40, 50, 4),
    (1, 50, 60, 5)

INSERT INTO #SectionTimeWindows (SectionTypeID, WindowStart, WindowEnd, RunningTime) VALUES
    (1, 00*60, 09*60, 30), -- 午夜到9点
    (1, 09*60, 18*60, 50), -- 9点到18点    
    (1, 18*60, 24*60, 5) -- 18点到午夜

DECLARE @Departure INT = 8 * 60 -- 整体出发时间，仅第一次迭代使用
DECLARE @CurrentTime INT = NULL -- 当前迭代的停靠时间
DECLARE @RunningTime INT = NULL -- 当前迭代匹配到的运行时长

DECLARE @CurrentSectionID INT;
DECLARE @CurrentSectionTypeID INT;
DECLARE @CurrentStartPlaceID INT;
DECLARE @CurrentEndPlaceID INT;

DECLARE @Order INT = 1;

DECLARE rowIterator CURSOR
    LOCAL FAST_FORWARD FOR
        SELECT SectionID, SectionTypeID, StartPlaceID, EndPlaceID
        FROM #Sections
        ORDER BY [Order]
OPEN rowIterator;

WHILE (1=1)
BEGIN
    FETCH NEXT FROM rowIterator INTO @CurrentSectionID, @CurrentSectionTypeID, @CurrentStartPlaceID, @CurrentEndPlaceID
    IF (@@FETCH_STATUS <> 0) BREAK;
        
    IF (@CurrentTime is null) -- 第一次迭代，无前置结果，使用出发时间作为第一个停靠时间
    BEGIN 
        SET @CurrentTime = @Departure
        INSERT INTO #StopTimes (PlaceID, IncommingSectionID, ArrivalTime, [Order]) 
        VALUES (@CurrentStartPlaceID, NULL, @CurrentTime, @Order)
    END

    SET @Order = @Order + 1
    -- 根据当前时间匹配对应行程类型的时间窗口，获取运行时长
    SELECT TOP 1 @RunningTime = RunningTime FROM #SectionTimeWindows 
        WHERE SectionTypeID = @CurrentSectionTypeID AND WindowStart <= @CurrentTime AND @CurrentTime < WindowEnd
    -- 更新当前时间为下一段的到达时间
    SET @CurrentTime = @CurrentTime + @RunningTime
    INSERT INTO #StopTimes (PlaceID, IncommingSectionID, ArrivalTime, [Order])
        VALUES (@CurrentEndPlaceID, @CurrentSectionID, @CurrentTime, @Order)
END
CLOSE rowIterator
DEALLOCATE rowIterator

SELECT StopTimeID, PlaceID, IncommingSectionID, 
ArrivalTime / 60 as Hours, ArrivalTime % 60 as Minutes,
ArrivalTime - LAG(ArrivalTime) Over (ORDER BY [Order]) as RunningTime
FROM #StopTimes


DROP TABLE #Sections
DROP TABLE #StopTimes
DROP TABLE #SectionTimeWindows

一、可以移除CURSOR：使用递归CTE替代 #

递归CTE是基于集合的操作，效率远高于逐行遍历的CURSOR，能大幅缩短百万级数据的处理时间。

实现代码 #

DECLARE @Departure INT = 8 * 60;

WITH RecursiveStopTimes AS (
    -- 锚点成员：生成第一个停靠记录（首段起点）
    SELECT 
        s.SectionID,
        s.SectionTypeID,
        s.StartPlaceID AS PlaceID,
        CAST(NULL AS INT) AS IncommingSectionID,
        @Departure AS ArrivalTime,
        s.[Order] AS StopOrder
    FROM #Sections s
    WHERE s.[Order] = 1

    UNION ALL

    -- 递归成员：计算后续每个停靠点的到达时间
    SELECT 
        next_s.SectionID,
        next_s.SectionTypeID,
        next_s.EndPlaceID AS PlaceID,
        curr_s.SectionID AS IncommingSectionID,
        curr_st.ArrivalTime + tw.RunningTime AS ArrivalTime,
        curr_st.StopOrder + 1 AS StopOrder
    FROM RecursiveStopTimes curr_st
    JOIN #Sections curr_s ON curr_s.SectionID = curr_st.SectionID
    JOIN #Sections next_s ON next_s.[Order] = curr_s.[Order] + 1
    JOIN #SectionTimeWindows tw 
        ON tw.SectionTypeID = curr_s.SectionTypeID
        AND tw.WindowStart <= curr_st.ArrivalTime 
        AND curr_st.ArrivalTime < tw.WindowEnd
)
-- 生成StopTimes表
SELECT 
    PlaceID,
    IncommingSectionID,
    ArrivalTime,
    StopOrder AS [Order]
INTO #StopTimes
FROM RecursiveStopTimes

-- 验证结果
SELECT StopTimeID, PlaceID, IncommingSectionID, 
ArrivalTime / 60 as Hours, ArrivalTime % 60 as Minutes,
ArrivalTime - LAG(ArrivalTime) Over (ORDER BY [Order]) as RunningTime
FROM #StopTimes
ORDER BY [Order];

逻辑说明 #

• 锚点成员生成首段起点的停靠记录，使用初始出发时间
• 递归成员通过关联前序结果，匹配对应时间窗口计算下一段的到达时间，自动遍历所有行程段
• 最终生成的StopTimes表包含N+1条记录，完全符合业务规则

二、若保留CURSOR，优化SELECT TOP 1操作 #

如果因业务限制必须保留CURSOR，可通过以下方式大幅提升每次FETCH中的查询效率：

1. 添加复合索引 #

针对SectionTimeWindows的查询条件创建复合索引，避免全表扫描：

CREATE NONCLUSTERED INDEX IX_SectionTimeWindows_Type_Window
ON #SectionTimeWindows (SectionTypeID, WindowStart, WindowEnd)
INCLUDE (RunningTime);

该索引让数据库可以快速通过SectionTypeID过滤，再利用WindowStart和WindowEnd的范围定位匹配窗口，直接返回RunningTime，无需回表。

2. 使用内存优化表 #

如果SectionTimeWindows数据量不大，可提前加载到内存优化表，减少磁盘IO：

-- 创建内存优化表
CREATE TABLE #MemSectionTimeWindows
(
    SectionTypeID INT,
    WindowStart INT,
    WindowEnd INT,
    RunningTime INT,
    INDEX IX_Mem_Type_Window NONCLUSTERED (SectionTypeID, WindowStart, WindowEnd)
) WITH (MEMORY_OPTIMIZED = ON);

-- 加载数据
INSERT INTO #MemSectionTimeWindows
SELECT SectionTypeID, WindowStart, WindowEnd, RunningTime FROM #SectionTimeWindows;

-- CURSOR循环中使用内存表查询
SET @RunningTime = (SELECT RunningTime FROM #MemSectionTimeWindows 
                    WHERE SectionTypeID = @CurrentSectionTypeID 
                    AND WindowStart <= @CurrentTime 
                    AND @CurrentTime < WindowEnd);

3. 语法优化：用SET替代SELECT TOP 1 #

在确保每个时间窗口匹配唯一的前提下，使用SET直接赋值，避免不必要的行扫描：

SET @RunningTime = (SELECT RunningTime FROM #SectionTimeWindows 
                    WHERE SectionTypeID = @CurrentSectionTypeID 
                    AND WindowStart <= @CurrentTime 
                    AND @CurrentTime < WindowEnd);

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