I knew I had rushed it earlier 😉 - this version packs 70,000 requests in about 13 seconds on my desktop.

Thanks for the test harness Dwain, I have borrowed and modified it for my own needs...

(although I never got your exact row count from it, I plumped for 70K requests as it is slightly more than you used, rather than slightly less)

Here is the build for the test tables/data

IF OBJECT_ID('tempdb..#tblSchedules') IS NOT NULL

DROP TABLE #tblSchedules;

GO

CREATE TABLE #tblSchedules

(

ScheduleID INT IDENTITY(1,1),

ScheduleType CHAR(2),

ScheduleLengthMinutes INT

)

GO

-- Note the different CI used here...

CREATE CLUSTERED INDEX ix_type ON #tblSchedules(ScheduleType,ScheduleID);

GO

IF OBJECT_ID('tempdb..#tblRequests') IS NOT NULL

DROP TABLE #tblRequests;

GO

CREATE TABLE #tblRequests

(

RequestID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

RequestType CHAR(2),

RequestLengthMinutes INT

)

GO

IF OBJECT_ID('tempdb..#tblScheduledRequests') IS NOT NULL

DROP TABLE #tblScheduledRequests;

GO

CREATE TABLE #tblScheduledRequests

(

-- Make RequestID an IDENTITY so we can use SCOPE_IDENTITY() to get the last value...see below for more

RequestID INT IDENTITY(0,1),

ScheduleID INT,

RequestChunkStartTime INT,

-- added to make checking schedule usage quicker

RequestLengthMinutes INT,

CONSTRAINT [PK_tblScheduledRequests] PRIMARY KEY CLUSTERED

(

[RequestID] ASC,

[ScheduleID] ASC

)

)

GO

-- Note the index on ScheduleID, including RequestLengthMinutes to speed up checking schedule usage...

CREATE INDEX ix_schedule ON #tblScheduledRequests(ScheduleID) include(RequestLengthMinutes);

GO

-- Populate 70K schedules

;WITH Tally (n) AS (

SELECT TOP 26 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

FROM sys.all_columns)

INSERT INTO #tblSchedules(ScheduleType,ScheduleLengthMinutes)

SELECT a, 15* (1+ABS(CHECKSUM(NEWID()))%16)

FROM (

SELECT a=SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ', n, 1)+b

FROM Tally a

CROSS JOIN (

SELECT b=SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ', n, 1) FROM Tally) b

) a

CROSS APPLY Tally b

CROSS APPLY (SELECT TOP 4 n FROM Tally) c

GO

-- Populate 70K Requests

;WITH Tally (n) AS (

SELECT TOP 26 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

FROM sys.all_columns)

INSERT INTO #tblRequests

SELECT a, 15* (1+ABS(CHECKSUM(NEWID()))%4)

FROM (

SELECT a=SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ', n, 1)+b

FROM Tally a

CROSS JOIN (

SELECT b=SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ', n, 1) FROM Tally) b

) a

CROSS APPLY Tally b

CROSS APPLY (SELECT TOP 4 n FROM Tally) c

GO

And my solution

-- Make sure the results are cleared down

TRUNCATE TABLE #tblScheduledRequests ;

-- Turn on identity insert so we can use SCOPE_IDENTITY() to get the last used RequestID

SET IDENTITY_INSERT #tblScheduledRequests ON;

-- Some code to ensure we reset the SCOPE_IDENTITY() value

INSERT #tblScheduledRequests(RequestID,ScheduleID)

VALUES(0,0);

TRUNCATE TABLE #tblScheduledRequests ;

-- just to get my first rowcount of 1 :)

SELECT

'Starting'

-- now keep adding results until we run out of requests or schedules

WHILE @@ROWCOUNT=1

INSERT #tblScheduledRequests(RequestID,ScheduleID,RequestChunkStartTime,RequestLengthMinutes)

SELECT TOP 1

RequestID,

ScheduleID,

ISNULL(AlreadyUsed,0),

RequestLengthMinutes

FROM

#tblSchedules MS

JOIN

#tblRequests MR

ON

ScheduleType = RequestType

-- Go figure out what we have already used for this Schedule

OUTER APPLY (

SELECT

SUM(TSR.RequestLengthMinutes)

FROM

#tblScheduledRequests TSR

WHERE

TSR.ScheduleID = MS.ScheduleID

) AS X(AlreadyUsed)

WHERE

-- Use the last inserted RequestID to seek to the next one we need

MR.RequestID > ISNULL(SCOPE_IDENTITY(),0)

AND

-- Only select a schedule that has enough remaining time

ISNULL(AlreadyUsed,0) + MR.requestLengthMinutes <= MS.ScheduleLengthMinutes

ORDER BY

-- Ensure we process Requests in the correct order

RequestID,ScheduleID

-- Turn off identity insert as we no longer need it

SET IDENTITY_INSERT #tblScheduledRequests OFF;

-- display the results

SELECT *

FROM #tblScheduledRequests tsr

order by RequestID