What made Scheduled Jobs faster in Optimizely CMS 13.3.0

The release notes for Optimizely CMS 13.3.0 include one short line:

Faster loading times for Scheduled Jobs page – The Scheduled Jobs list and the History tab for each scheduled job load faster, especially when the list is large.

I wanted to know what was behind it, so I decompiled 13.2.0 and 13.3.0 and compared them. None of the scheduled-jobs C# code changed. The whole improvement is in the database. One rewritten query and one new index.

The query

Each job's history is read by netSchedulerListLog, a SQL file embedded in EPiServer.dll. In 13.2.0 it numbered every log row for the job with ROW_NUMBER() and then filtered out one page:

;WITH Items_CTE AS (
    SELECT [Exec], [Status], [Text], [Duration], [Trigger], [Server],
        ROW_NUMBER() OVER (ORDER BY [Exec] DESC) AS [RowIndex]
    FROM tblScheduledItemLog
    WHERE fkScheduledItemId = @pkID
)
SELECT TOP (@maxCount) ... FROM Items_CTE WHERE RowIndex >= @startIndex

In 13.3.0 it's a plain OFFSET/FETCH:

SELECT [Exec], [Status], [Text], [Duration], [Trigger], [Server], @TotalCount AS 'TotalCount'
FROM tblScheduledItemLog
WHERE fkScheduledItemId = @pkID
ORDER BY [Exec] DESC
OFFSET IIF(@startIndex > 0, @startIndex - 1, 0) ROWS
FETCH NEXT @maxCount ROWS ONLY;

The old outer SELECT TOP had no ORDER BY, so SQL Server never actually guaranteed that the history came back newest first. The new query does.

The index

The new schema upgrade script, 21.0.5.sql, replaces the old index on tblScheduledItemLog. The old one only covered fkScheduledItemId. The new one is ordered by job and execution time, and it includes every column the query reads:

CREATE NONCLUSTERED INDEX [IX_tblScheduledItemLog_fkScheduledItemId]
ON [dbo].[tblScheduledItemLog] ([fkScheduledItemId] ASC, [Exec] DESC)
INCLUDE ([Text], [Duration], [Server], [Trigger], [Status])

Before, SQL Server had to find all of a job's log rows, look up each one in the table and sort them all, just to return one page. That can be up to 1,000 rows per job, which is the default retention. Now it seeks to the job, reads the rows newest first and stops when the page is full.

 Why the list got faster too

The jobs list shows the duration of each job's last run. That value isn't stored on the job, so for every job the controller runs the history query and asks for just the newest entry.

var last = (await _scheduledJobLogRepository.GetAsync(job.ID, 0L, 1)).PagedResult.FirstOrDefault();

With the old index, getting that one row meant fetching and sorting all of the job's history, which can be up to 1,000 rows. That happened once per job, every time the page loaded. With the new index, the newest row is simply the first one SQL Server reads. The page still runs one history query per job, but each one is now nearly free. That explains "especially when the list is large".