Puzzle / CTE Help - NO CURORS :)

  • 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

  • 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.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • 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

  • 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

  • Thanks Kevin

    I'll take a look 🙂

  • 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.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • 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.

  • 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.

  • 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 int = 0

    update #wrkSchedules

    set @S = ScheduleEnd = case when ScheduleSeq = 1 then 0 else @S end + ScheduleLengthMinutes,

    ScheduleStart = @S - ScheduleLengthMinutes

    select * from #wrkSchedules

    update #wrkRequests

    set @S = RequestEnd = case when RequestSeq = 1 then 0 else @S end + RequestLengthMinutes,

    RequestStart = @S - 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.

  • 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.

  • 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);

  • Forum Etiquette: How to post Reporting Services problems
  • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • [/url]
  • How to Post Performance Problems - by Gail Shaw
  • [/url]

  • 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.

    @colin,

    I have to agree with what Paul suggests above. Is the use of CLR an option that you can live with?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    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

  • 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);

  • Forum Etiquette: How to post Reporting Services problems
  • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • [/url]
  • How to Post Performance Problems - by Gail Shaw
  • [/url]

  • 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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    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