We have a couple of tables, let’s call them Sections and SectionTimeWindows. Sections table contains data about sections of ‘movement’, which starts at a place, ends at a place and has a type. The type is important, because it determines how much time will a section take – every section type has a set of time windows and the window determines the running time of a section based on when we arrive at the section.

We need a new table, where StopTimes are recorded, provided we know a DepartureTime. We start with a stop at the beginning of the first section at StopTime=DepartureTime, then determine RunningTime for every section based on previous StopTime, and we insert the StopTime as new row. This means, that the RunningTime and therefore the StopTime values are based on results computed for previous row(s).

Our current implementation uses a CURSOR to iterate over Sections, keep current StopTime, probe the relevant window for next RunningTime, add RunningTime to StopTime and INSERT.

A simplified version of our code is this:

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,
    StopTime 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), -- from midnight to 9AM 
    (1, 09*60, 18*60, 50), -- from 9AM to 18AM    
    (1, 18*60, 24*60, 5) --- dtto

DECLARE @Departure INT = 8 * 60
DECLARE @Probe INT = 0
DECLARE @StopTime 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 (@StopTime is null)
    BEGIN 
        SET @StopTime = @Departure
        INSERT INTO #StopTimes (PlaceID, IncommingSectionID, StopTime, [Order]) 
        VALUES (@CurrentStartPlaceID, NULL, @StopTime, @Order)
    END
    
    SET @Order = @Order + 1
    SET @Probe = @StopTime
    SELECT TOP 1 @RunningTime = RunningTime FROM #SectionTimeWindows 
        WHERE SectionTypeID = @CurrentSectionTypeID AND WindowStart <= @Probe AND @Probe < WindowEnd
    SET @StopTime = @StopTime + @RunningTime
    INSERT INTO #StopTimes (PlaceID, IncommingSectionID, StopTime, [Order])
        VALUES (@CurrentEndPlaceID, @CurrentSectionID, @StopTime, @Order)
END
CLOSE rowIterator
DEALLOCATE rowIterator

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


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

The real structure is a bit more complicated, the data is also grouped by MovementID and apart from the StopTime, we also track several other properties. We may also wait at a StopPlace, which delays the departure to the next section as well. But this is the gist of it.

The real procedure takes up to 6 hours to compute (real Sections table has around 10^6 rows and SectionTimeWindows table has around 10^7 rows). The issue is most likely the SELECT in every FETCH iteration, but I don’t know how to JOIN the SectionTimeWindows if I don’t have the Probe to do it.

I’ve tried to approach the problem using LAG, but got nowhere, as the previous row’s StopTime is a result of it’s previous row’s StopTime recursively. I have the same issue with SUM OVER(PARTITION..RANGE..), but again, I have no idea how to JOIN the window. I would need some kind of SUM(SELECT…WHERE PROBE)…

Is there any way to remove this kind of CURSOR? And if not, is there any way to speed up the SELECT TOP 1?