February 12, 2013 at 8:56 am
I have a use-case and I know a CTE is probably where I'm gonna go with this but I just can't get it right due to the logic and I thought I would throw it out here and see what you amazingly smart genius type folks could come up with.
🙂 (figured I should butter you up a bit)
I know I can do this with a cursor but this is going to need to work with millions of rows and the cursor is HORRIBLY slow (as expected but does work)
NO CURSOR PLS
OK so....
I have Schedules with a Type and a block of time allocated (in minutes)
I have Request with a Type and a block of time requested (in minutes)
In the order of the RequestID (the order they came in) I need to find the first available ScheduleID of the same type that has enough time remaining to handle the Request
There can be multiple Requests that end up filling (or partially filling) a Schedule
ie)
Request 1 (15 minutes), Request 2 (30 minutes), Request 3 (15 Minutes) of Type A can all fit into Schedule 1 of Type A (that has 60 minutes allocated)
Attached is a script with the setup of the tables and a small sample of data as well as a picture (I'm a picture person) of how logically the schedules and requests should fall.
How it should go:
RequestID 1 of Type A and length of 15 minutes gets slotted into Schedule 1 (Type A, 30 minutes)
RequestID 2 of Type B and length of 15 minutes gets slotted into Schedule 2 (Type B, 60 minutes)
RequestID 3 of Type A and length of 30 minutes gets slotted into Schedule 3 because Schedule 1 already has the first 15 minutes allocated to RequestID 1
RequestID 4 of Type B and length of 30 minutes get slotted into Schedule 2 because Schedule 2 still has 45 minutes remaining that can be allocated
RequestID 5 of Type C and length of 30 minutes get slotted into Schedule 4 (Type C, 45 minutes)
RequestID 6 of Type C and length of 15 minutes get slotted into Schedule 4 because Schedule 4 still has 15 minutes remaining that can be allocated
RequestID 7 of Type B and length of 30 minutes DOES NOT get a schedule because the only available schedule of type B only as 15 minutes left (due to RequestID 2 and RequestID 4)
tblScheduledRequests
Should look like this
RequestID ScheduleID RequestChunkStartTime
1, 1, 0
2, 2, 0
3, 3, 0
4, 2, 15
5, 4, 0
6, 4, 30
Keeping in mind the "RequestChunkStartTime" is when the RequestID will start in the ScheduleID (ScheduleLength)
The goal is to respect the RequestID order as priority.... Even though ScheduleID 2 (Type B) can be filled 100% with RequestID 4 and 7... we have to take RequestID 2 first, RequestID 4 second, and RequestID 7 doesn't get a ScheduleID
February 12, 2013 at 9:14 am
If the sequential aspect is important (i.e. FIRST request must go to FIRST available schedule), then I think a cursor will be faster than any of the alternatives. But make it one cursor (for the requests). Don't use a cursor to find the first available schedule. And if it ruuns slow, check if the query to find the first available schedule is efficient. Having a column RemainingTime in the Schedules table can help, having an index on (ScheduleType, RemainingTime) may help even more - but check that the overhead of updating this column when adding a session to a schedule doesn't hurt performance more it helps the search query.
If, on the other hand, you are looking for an algorithm to pack as many sessions in the available schedules, read my series of blog posts about "bin packing" (google for "bin packing Hugo Kornelis") - especially part 5. It's not exactly the same, but hopefully close enough to give you some pointers. The trick I used to speed up the process is to use a loop that in each iteration assigns all available schedulers a session. Maybe you can work that into your situation? However, this will totally change the order in which sessions are assigned to schedulers, so you can't use this if the order matters.
February 12, 2013 at 9:19 am
yeah i have cursor code with the column (time remaining), etc just as you suggested and I'm just not a fan of it or it's peformance.
It's an interesting use-case and I'm going to spend some more time on it cause I just don't believe that a cursor is my only solution 🙂
Thanks for looking
February 12, 2013 at 9:21 am
Colin, sorry I don't have time to dig into this more, but see if this sample code I have from one of my SQL Saturday sessions can point you in the right direction. NOTE: proper indexing can make this a LOT faster on large datasets:
-- Suppress data loading messages
SET NOCOUNT ON;
DECLARE @Schedule table
( AppID int IDENTITY,
AppTeam varchar(20),
AppStart datetime,
AppFinish datetime
);
INSERT INTO @Schedule VALUES ( 'Start', NULL, '01/11/2007 09:00' );
INSERT INTO @Schedule VALUES ( 'Smith', '01/11/2007 09:00', '01/11/2007 09:30' );
INSERT INTO @Schedule VALUES ( 'Smith', '01/11/2007 10:00', '01/11/2007 10:15' );
INSERT INTO @Schedule VALUES ( 'Jones', '01/11/2007 11:00', '01/11/2007 12:00' );
INSERT INTO @Schedule VALUES ( 'Williams', '01/11/2007 12:00', '01/11/2007 14:45' );
INSERT INTO @Schedule VALUES ( 'Hsiao', '01/11/2007 15:30', '01/11/2007 16:00' );
INSERT INTO @Schedule VALUES ( 'Lopez', '01/11/2007 16:00', '01/11/2007 17:30' );
INSERT INTO @Schedule VALUES ( 'Green', '01/11/2007 17:30', '01/11/2007 18:30' );
INSERT INTO @Schedule VALUES ( 'Alphonso', '01/11/2007 20:00', '01/11/2007 20:30' );
INSERT INTO @Schedule VALUES ( 'End', '01/11/2007 21:00', NULL );
-- Determine the Length of Time Required
DECLARE @ApptNeed int;
SET @ApptNeed = 45;
--SET @ApptNeed = 60; --comment out Lopez and run this one
--Find FIRST Available Time Slot
;WITH CTE
AS ( SELECT
*,
RowNumber = ROW_NUMBER() OVER( ORDER BY AppStart ASC )
FROM @Schedule
)
SELECT FirstApptAvail = min( a.AppFinish )
FROM CTE a
INNER JOIN CTE b --be SURE you cover endpoints on self-joins like this!!
ON a.RowNumber = b.RowNumber - 1
WHERE datediff( minute, a.AppFinish, b.AppStart) >= @ApptNeed;
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 12, 2013 at 9:23 am
February 12, 2013 at 9:26 am
Colin,
Forgive me, but I'm a little confused on the scenario. Am I reading this correctly that your schedules table is not a distinct record set but will always have duplicate schedule types in it?
How do schedules get inserted into the table? What creates them? Or is there a set number of schedule types in tblSchedules?
Kevin's code probably already gave you the answer, but I'd like to look at this in detail myself. The answers to these questions would help me figure out my own approach to the problem.
February 12, 2013 at 9:33 am
If I'm understanding it, the requirement as specified sounds fundamentally row-by-row as you must know what the previous row in the sequence did in order to process the next row, so a recursive CTE is likely to be as bad or worse than a well optimised cursor.
As Hugo says, if there was a less rigid requirement for the priority of each request, then you could perform recursion/looping over sets rather than over rows, massively reducing the iteration and improving the performance.
I think Kevin's code helps with finding the first individual gap in a schedule, but not in fitting a batch of requests into a schedule in a specific priority order.
February 12, 2013 at 9:34 am
Hi Brandie
1st thanks for reading 🙂
2nd I "simplified" the Schedule table down to 3 columns for the sake of trying not to confuse the goals (mission failed apparently hehehe)
the actual table would have a date column (ScheduleDateTime) and an FK to a ScheduleType table, etc
I tried to keep the column count down and through the example data and picture try and really just focus on the goal as the other columns are not relevant to the use-case and I thought they would confuse.
February 12, 2013 at 2:18 pm
Hi
Here's something that may work, I still need to test it over a larger set of data.
It uses quirky updates and I haven't done a lot with these, so I may not have applied the rules correctly.
Hopefully the process I have used is clear.
I'm creating tables with cumulative start and end times for the schedules and requests.
Then I determine apply a cumulative offset to requests where their start and end times span an end time for a schedule.
Once that is done you can get the schedule based on the end request time + offset.
CREATE TABLE #wrkSchedules (
ScheduleType char(10) not null,
ScheduleID int not null,
ScheduleSeq int not null,
ScheduleLengthMinutes int not null,
ScheduleStart int,
ScheduleEnd int
)
ALTER TABLE #wrkSchedules ADD CONSTRAINT ws_pk PRIMARY KEY (ScheduleType, ScheduleSeq)
CREATE TABLE #wrkRequests (
RequestType char(10) not null,
RequestID int not null,
RequestSeq int not null,
RequestLengthMinutes int not null,
RequestStart int,
RequestEnd int,
Offset int,
ScheduleID int
)
ALTER TABLE #wrkRequests ADD CONSTRAINT wr_pk PRIMARY KEY (RequestType, RequestSeq)
INSERT INTO #wrkSchedules (scheduleType,ScheduleID,ScheduleLengthMinutes,ScheduleSeq)
SELECT scheduleType,ScheduleID,ScheduleLengthMinutes, row_number() over (partition by scheduleType order by ScheduleID) ScheduleSeq
FROM tblSchedules
INSERT INTO #wrkRequests (RequestType,RequestID,RequestLengthMinutes,RequestSeq)
SELECT RequestType,RequestID,RequestLengthMinutes, row_number() over (partition by RequestType order by RequestID) RequestSeq
FROM tblRequests
declare @s-2 int = 0
update #wrkSchedules
set @s-2 = ScheduleEnd = case when ScheduleSeq = 1 then 0 else @s-2 end + ScheduleLengthMinutes,
ScheduleStart = @s-2 - ScheduleLengthMinutes
select * from #wrkSchedules
update #wrkRequests
set @s-2 = RequestEnd = case when RequestSeq = 1 then 0 else @s-2 end + RequestLengthMinutes,
RequestStart = @s-2 - RequestLengthMinutes
select * from #wrkRequests
declare @o int = 0
-- Altered to get Maximum ScheduleEnd
update #wrkRequests
set @o = offset =
case when RequestStart = 0 then
isnull((select MAX(ScheduleEnd) - RequestStart from #wrkSchedules w where w.scheduletype = requesttype and w.ScheduleEnd between RequestStart and RequestEnd - 1), 0)
else
@o + isnull((select MAX(ScheduleEnd) - RequestStart from #wrkSchedules w where w.scheduletype = requesttype and w.ScheduleEnd between RequestStart + @o and RequestEnd + @o - 1), 0)
end
select * from #wrkRequests
update wr
set wr.ScheduleID = ws.ScheduleID
from #wrkRequests wr
inner join #wrkSchedules ws
on wr.RequestType = ws.ScheduleType
and (wr.requestStart + offset) between ws.ScheduleStart and ws.ScheduleEnd - 1
and (wr.requestEnd + offset) between ws.ScheduleStart + 1 and ws.ScheduleEnd
select * from #wrkRequests
select * from #wrkSchedules
select RequestID,
wr.ScheduleID,
(RequestStart + offset) - ScheduleStart RequestChunkStartTime
from #wrkRequests wr
inner join #wrkSchedules ws on wr.ScheduleID = ws.ScheduleID
Edited after running with data from mister.magoo's generator to handle requests the span more than a single schedule.
February 12, 2013 at 4:13 pm
Hi Colin,
It's quite easy to go mad trying to do this sort of thing in T-SQL today (future things like ordered aggregates might help, I don't know).
A cursor solution is straightforward, the only downside being that T-SQL cursors suck performance-wise. So, I would use a CLR cursor: the obvious procedural logic in a CLR stored procedure that reads from Schedules and Requests and returns matched output as a set that can be directly INSERT...EXEC'd into the Scheduled Requests table. If the output set is huge, you could even use bulk copy from the CLR code.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 12, 2013 at 4:46 pm
No cursors...but a WHILE LOOP...so :hehe:
SELECT
'Starting' -- just to get my first rowcount of 1 :)
WHILE @@ROWCOUNT=1
INSERT tblScheduledRequests(RequestID,ScheduleID,RequestChunkStartTime)
SELECT TOP 1
RequestID,
ScheduleID,
ISNULL(AlreadyUsed,0)
FROM
tblSchedules MS
JOIN
tblRequests MR
ON
ScheduleType = RequestType
AND
RequestLengthMinutes <= ScheduleLengthMinutes
OUTER APPLY (
-- Go figure out what we have already used for this Schedule
SELECT
SUM(TR.RequestLengthMinutes)
FROM
tblScheduledRequests TSR
-- this lookup can be removed if we added RequestLengthMinutes to tblScheduledRequests
JOIN
tblRequests TR
ON
TR.RequestID = TSR.RequestID
WHERE
TSR.ScheduleID = MS.ScheduleID
) AS X(AlreadyUsed)
WHERE
NOT EXISTS (SELECT 1 FROM tblScheduledRequests TSR WHERE TSR.RequestID = MR.RequestID)
AND
ISNULL(AlreadyUsed,0) + MR.requestLengthMinutes <= MS.ScheduleLengthMinutes
ORDER BY
RequestID,ScheduleID
SELECT *
FROM tblScheduledRequests
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);February 12, 2013 at 8:46 pm
Paul White (2/12/2013)
Hi Colin,It's quite easy to go mad trying to do this sort of thing in T-SQL today (future things like ordered aggregates might help, I don't know).
A cursor solution is straightforward, the only downside being that T-SQL cursors suck performance-wise. So, I would use a CLR cursor: the obvious procedural logic in a CLR stored procedure that reads from Schedules and Requests and returns matched output as a set that can be directly INSERT...EXEC'd into the Scheduled Requests table. If the output set is huge, you could even use bulk copy from the CLR code.
I have to agree with what Paul suggests above. Is the use of CLR an option that you can live with?
--Jeff Moden
Change is inevitable... Change for the better is not.
February 12, 2013 at 10:12 pm
Colin,
Congratulations on getting the attention so quickly of the master of bin-packing (Hugo Kornelis). I am but a shadowy, wannabe player in this space.
Nonetheless, I shall attempt to contribute a potential solution that may be a contender for a cursor-based solution. I think it would depend on the number of bins (schedules) you need to pack. Give it a try and let me know how it stacks up.
CREATE TABLE #tblSchedules
(
ScheduleID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
ScheduleType CHAR(2),
ScheduleLengthMinutes INT,
StartTime INT
)
GO
--CREATE INDEX IDX1 ON #tblSchedules(ScheduleType) INCLUDE(ScheduleLengthMinutes);
--GO
CREATE TABLE #tblRequests
(
RequestID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
RequestType CHAR(2),
RequestLengthMinutes INT
)
GO
--CREATE INDEX IDX1 ON #tblRequests(RequestType) INCLUDE(RequestLengthMinutes);
--GO
CREATE TABLE #tblScheduledRequests
(
RequestID INT,
ScheduleID INT,
RequestChunkStartTime INT,
CONSTRAINT [PK_tblScheduledRequests] PRIMARY KEY CLUSTERED
(
[RequestID] ASC,
[ScheduleID] ASC
)
)
GO
INSERT INTO #tblSchedules SELECT'A', 30, 0
INSERT INTO #tblSchedules SELECT'B', 60, 0
INSERT INTO #tblSchedules SELECT'A', 30, 0
INSERT INTO #tblSchedules SELECT'C', 45, 0
--;WITH Tally (n) AS (
-- SELECT TOP 26 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
-- FROM sys.all_columns)
--INSERT INTO #tblSchedules
--SELECT a, 15* (1+ABS(CHECKSUM(NEWID()))%16), 0
--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 Tally c
GO
INSERT INTO #tblRequests SELECT 'A', 15
INSERT INTO #tblRequests SELECT 'B', 15
INSERT INTO #tblRequests SELECT 'A', 30
INSERT INTO #tblRequests SELECT 'B', 30
INSERT INTO #tblRequests SELECT 'C', 30
INSERT INTO #tblRequests SELECT 'C', 15
INSERT INTO #tblRequests SELECT 'B', 30
--;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 Tally c --(SELECT TOP 18 n FROM Tally) c
GO
CREATE TABLE #Requests
(
RequestID INT PRIMARY KEY CLUSTERED,
RequestType CHAR(2),
RequestLengthMinutes INT
)
DECLARE @RowCount INT = 1
,@StartDT DATETIME = GETDATE(), @Count INT = 1, @ScheduledRequests INT
SET NOCOUNT ON
WHILE @RowCount <> 0
BEGIN
INSERT INTO #Requests
SELECT RequestID, RequestType, RequestLengthMinutes
FROM (
SELECT RequestID, RequestType, RequestLengthMinutes
,n=ROW_NUMBER() OVER (PARTITION BY RequestType ORDER BY RequestID)
FROM #tblRequests
WHERE RequestID NOT IN (SELECT RequestID FROM #tblScheduledRequests)
) a
WHERE n=1
;WITH Schedules AS (
SELECT ScheduleID, ScheduleType, ScheduleLengthMinutes, StartTime
FROM (
SELECT ScheduleID, ScheduleType, ScheduleLengthMinutes, StartTime
,n=ROW_NUMBER() OVER (PARTITION BY ScheduleType ORDER BY ScheduleID)
FROM #tblSchedules a
INNER JOIN #Requests b
ON b.RequestLengthMinutes <= a.ScheduleLengthMinutes AND
b.RequestType = a.ScheduleType
) a
WHERE n = 1
)
UPDATE a
SET ScheduleLengthMinutes = ScheduleLengthMinutes - RequestLengthMinutes
,StartTime = a.StartTime + RequestLengthMinutes
OUTPUT b.RequestID, INSERTED.ScheduleID, DELETED.StartTime
INTO #tblScheduledRequests
FROM Schedules a
INNER JOIN #Requests b ON a.ScheduleType = b.RequestType
SELECT @RowCount = @@ROWCOUNT
SELECT @ScheduledRequests = (SELECT COUNT(*) FROM #tblScheduledRequests)
PRINT 'Pass #: ' + CAST(@Count AS VARCHAR(10)) + ' MS:' +
CAST(DATEDIFF(millisecond, @StartDT, GETDATE()) AS VARCHAR(10)) +
' Scheduled Requests: ' + CAST(@ScheduledRequests AS VARCHAR(10))
SELECT @Count = @Count + 1, @StartDT = GETDATE()
TRUNCATE TABLE #Requests
END
SELECT * FROM #tblScheduledRequests
--SELECT * FROM #tblSchedules
--SELECT * FROM #tblRequests
ALTER TABLE #tblScheduledRequests DROP CONSTRAINT [PK_tblScheduledRequests]
DROP TABLE #tblSchedules
DROP TABLE #tblRequests
DROP TABLE #tblScheduledRequests
DROP TABLE #Requests
Note that I added a helper column to your tblSchedules and did it all with temp tables to keep my sandbox clean. Hope I got the RequestChunkStart time column right for your needs.
Edit: Improved the speed of what I originally posted a bit and included a test harness that you can uncomment and use to process 59,341 requests, which runs in about 2.5 minutes on my laptop.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 13, 2013 at 5:43 pm
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
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);February 13, 2013 at 6:09 pm
Magoo you've done it again!
Pretty fast for sure.
The record counts I posted might have been before I made some changes to the test harness.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply