Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Puzzle / CTE Help - NO CURORS :) Expand / Collapse
Author
Message
Posted Tuesday, February 12, 2013 4:46 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:10 AM
Points: 1,787, Visits: 5,724
No cursors...but a WHILE LOOP...so


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


  • MMGrid Addin
  • MMNose Addin


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

  • Post #1419237
    Posted Tuesday, February 12, 2013 8:46 PM


    SSC-Dedicated

    SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

    Group: General Forum Members
    Last Login: Today @ 9:16 PM
    Points: 37,102, Visits: 31,655
    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."

    (play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

    Helpful Links:
    How to post code problems
    How to post performance problems
    Post #1419282
    Posted Tuesday, February 12, 2013 10:12 PM


    Hall of Fame

    Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

    Group: General Forum Members
    Last Login: Today @ 7:42 PM
    Points: 3,648, Visits: 5,327
    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!

    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?
    Since random numbers are too important to be left to chance, let's generate some!
    Learn to understand recursive CTEs by example.
    Splitting strings based on patterns can be fast!
    Post #1419289
    Posted Wednesday, February 13, 2013 5:43 PM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Today @ 1:10 AM
    Points: 1,787, Visits: 5,724
    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


  • MMGrid Addin
  • MMNose Addin


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

  • Post #1419781
    Posted Wednesday, February 13, 2013 6:09 PM


    Hall of Fame

    Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

    Group: General Forum Members
    Last Login: Today @ 7:42 PM
    Points: 3,648, Visits: 5,327
    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!

    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?
    Since random numbers are too important to be left to chance, let's generate some!
    Learn to understand recursive CTEs by example.
    Splitting strings based on patterns can be fast!
    Post #1419785
    Posted Wednesday, February 13, 2013 10:28 PM


    SSChampion

    SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

    Group: General Forum Members
    Last Login: Today @ 11:03 AM
    Points: 11,194, Visits: 11,167
    mister.magoo (2/13/2013)
    this version packs 70,000 requests in about 13 seconds on my desktop.

    Debug version SQLCLR procedure (without bulk copy) loading the destination table: 1.4s on the same data set.
    Time to return the 70,304 result rows to the SSMS grid: 750ms.
    CLR cursors: priceless




    Paul White
    SQL Server MVP
    SQLblog.com
    @SQL_Kiwi
    Post #1419814
    Posted Thursday, February 14, 2013 2:02 AM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Today @ 1:10 AM
    Points: 1,787, Visits: 5,724
    Paul White (2/13/2013)
    mister.magoo (2/13/2013)
    this version packs 70,000 requests in about 13 seconds on my desktop.

    Debug version SQLCLR procedure (without bulk copy) loading the destination table: 1.4s on the same data set.
    Time to return the 70,304 result rows to the SSMS grid: 750ms.
    CLR cursors: priceless


    Hi Paul,

    By priceless, I am hoping you mean free?

    Are you able to share that code, because I would love to learn from it?

    Thanks


    MM


  • MMGrid Addin
  • MMNose Addin


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

  • Post #1419875
    Posted Thursday, February 14, 2013 4:55 AM


    SSChampion

    SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

    Group: General Forum Members
    Last Login: Today @ 11:03 AM
    Points: 11,194, Visits: 11,167
    mister.magoo (2/14/2013)
    Are you able to share that code, because I would love to learn from it?

    Here you go:

    USE Sandpit;
    GO
    /***********************
    ** Tables and indexes
    ************************/
    CREATE TABLE [dbo].[tblRequests] (
    [RequestID] INT IDENTITY (1, 1) NOT NULL,
    [RequestType] CHAR (2) NULL,
    [RequestLengthMinutes] INT NULL,
    PRIMARY KEY CLUSTERED ([RequestID] ASC)
    );
    GO
    CREATE NONCLUSTERED INDEX [IX_tblRequests_RequestType_RequestID]
    ON [dbo].[tblRequests]([RequestType] ASC, [RequestID] ASC)
    INCLUDE([RequestLengthMinutes]);
    GO
    CREATE TABLE [dbo].[tblScheduledRequests] (
    [RequestID] INT NOT NULL,
    [ScheduleID] INT NOT NULL,
    [RequestChunkStartTime] INT NULL,
    CONSTRAINT [PK_tblScheduledRequests] PRIMARY KEY CLUSTERED ([RequestID] ASC, [ScheduleID] ASC)
    );
    GO
    CREATE TABLE [dbo].[tblSchedules] (
    [ScheduleID] INT IDENTITY (1, 1) NOT NULL,
    [ScheduleType] CHAR (2) NULL,
    [ScheduleLengthMinutes] INT NULL,
    PRIMARY KEY CLUSTERED ([ScheduleID] ASC)
    );
    GO
    CREATE NONCLUSTERED INDEX [IX_tblSchedules_ScheduleType_ScheduleID]
    ON [dbo].[tblSchedules]([ScheduleType] ASC, [ScheduleID] ASC)
    INCLUDE([ScheduleLengthMinutes]);
    GO
    /***********************
    ** SQLCLR procedure
    ************************/
    ALTER DATABASE Sandpit
    SET TRUSTWORTHY ON;
    GO
    CREATE ASSEMBLY [Scheduling]
    AUTHORIZATION [dbo]
    FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C01030055CD1C510000000000000000E00002210B010B000016000000060000000000009E3400000020000000400000000000100020000000020000040000000000000004000000000000000080000000020000000000000300408500001000001000000000100000100000000000001000000000000000000000004C3400004F00000000400000B002000000000000000000000000000000000000006000000C000000143300001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000A4140000002000000016000000020000000000000000000000000000200000602E72737263000000B0020000004000000004000000180000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001C000000000000000000000000000040000042000000000000000000000000000000008034000000000000480000000200050060240000B40E000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001B30040037030000010000117201000070730500000A0A066F0600000A730700000A0B066F0800000A0C0872350000706F0900000A086F0A00000A72D00000701F0C2080000000730B00000A6F0C00000A186F0D00000A086F0A00000A72D80000701F0C2080000000730B00000A6F0C00000A186F0D00000A086F0E00000A26086F0A00000A72D00000706F0F00000A6F1000000A74110000010D0972E00000706F1100000A1304071104152E1972F40000700911046F1200000A720C010070281300000A2B0C07092513166F1400000A11166F1400000A07086F0A00000A72D80000706F0F00000A6F1000000A74110000016F1500000A07176F1600000A07166F1700000ADE0A082C06086F1800000ADC076F1900000A730500000A1305076F1900000A730500000A130611056F0600000A11066F0600000A11066F0800000A130711056F0800000A1308110772240100706F0900000A110872010400706F0900000A198D14000001131711171672D00600701E731A00000AA211171772E40600701E731A00000AA211171872FA0600701E731A00000AA2111713091109731B00000A130A281C00000A130B110B110A6F1D00000A11076F1E00000A130C11086F1E00000A130D17130E110D6F1F00000A3904010000110C6F1F00000A39F8000000110D166F2000000A130F110D176F2100000A1310110D186F2100000A1311110C166F2000000A1312110C176F2100000A1313110C186F2100000A131416131538AD0000001112110F282200000A2C3D1114111132371115111158131511141115591314110A1611106F2300000A110A1711136F2300000A110A1811156F2300000A110B110A6F2400000A2B3B1112110F6F2500000A16302F110C6F1F00000A25130E2C4D110C166F2000000A1312110C176F2100000A1313110C186F2100000A13141613152B2A110D6F1F00000A25130E2C1E110D166F2000000A130F110D176F2100000A1310110D186F2100000A1311110E3A4CFFFFFFDE0C110D2C07110D6F1800000ADCDE0C110C2C07110C6F1800000ADC110B6F2600000ADE0C11082C0711086F1800000ADCDE0C11072C0711076F1800000ADCDE0C11062C0711066F1800000ADCDE0C11052C0711056F1800000ADCDE0A062C06066F1800000ADC2A0041C40000020000001E000000DE000000FC0000000A0000000000000002000000BC01000015010000D10200000C0000000000000002000000B30100002C010000DF0200000C000000000000000200000040010000B4010000F40200000C000000000000000200000037010000CB010000020300000C000000000000000200000020010000F0010000100300000C0000000000000002000000130100000B0200001E0300000C00000000000000020000000B000000210300002C0300000A000000000000001E02282700000A2A42534A4201000100000000000C00000076322E302E35303732370000000005006C00000058020000237E0000C4020000C803000023537472696E6773000000008C0600001C07000023555300A80D0000100000002347554944000000B80D0000FC00000023426C6F620000000000000002000001471402000900000000FA25330016000001000000190000000200000002000000270000000400000001000000010000000200000000000A0001000000000006003A00330006006B0058000B007F0000000600AE008E000600CE008E000A001E0103010A004A0134010A006B0158010A007D0134010A00980134010A00B10158010A00CB0134010A00F10134010A00FE01F7000A000C0258010A001802F70006005C0233000600C00233000A00D40258010A00030303010A000F0303010A001D0303010A00280303010A004A0334010A006603580100000000010000000000010001000100100019000000050001000100502000000000960041000A000100582400000000861852000E000100110052001200210052001800290052000E00310052000E00390052002200410078010E00490052000E003900A30127005900BB0122005100E2012C0069005200310061000802390079002B024000590039024600610049024A0079005202500089006302540089006B025900890075025E0049007C02220049008B02220049009E0265004900B50265009100CC020E009900EE026A00A10052006E00A90052007500B10030037C00B90039038100510058038700C90073038C00C90078035900C9008203900089008B039500A90097039B00B900A00381008900AF035400B900B9030E00090052000E00200023001D002E000B00CB002E001300D4002E001B00DD00A100048000000000000000000000000000000000EC00000002000000000000000000000001002A00000000000200000000000000000000000100F700000000000000003C4D6F64756C653E005363686564756C696E672E646C6C0053746F72656450726F63656475726573006D73636F726C69620053797374656D004F626A656374005363686564756C655265717565737473002E63746F720053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465005363686564756C696E670053797374656D2E44617461004D6963726F736F66742E53716C5365727665722E5365727665720053716C50726F6365647572654174747269627574650053797374656D2E446174612E53716C436C69656E740053716C436F6E6E656374696F6E0053797374656D2E446174612E436F6D6D6F6E004462436F6E6E656374696F6E004F70656E0053716C436F6E6E656374696F6E537472696E674275696C6465720053716C436F6D6D616E6400437265617465436F6D6D616E64004462436F6D6D616E64007365745F436F6D6D616E64546578740053716C506172616D65746572436F6C6C656374696F6E006765745F506172616D65746572730053716C506172616D657465720053716C44625479706500416464004462506172616D6574657200506172616D65746572446972656374696F6E007365745F446972656374696F6E00457865637574654E6F6E5175657279006765745F4974656D006765745F56616C756500537472696E6700496E6465784F6600537562737472696E6700436F6E636174007365745F44617461536F75726365007365745F496E697469616C436174616C6F67007365745F496E74656772617465645365637572697479007365745F456E6C6973740049446973706F7361626C6500446973706F7365004462436F6E6E656374696F6E537472696E674275696C646572006765745F436F6E6E656374696F6E537472696E670053716C4D657461446174610053716C446174615265636F72640053716C436F6E746578740053716C50697065006765745F506970650053656E64526573756C747353746172740053716C44617461526561646572004578656375746552656164657200446244617461526561646572005265616400476574537472696E6700476574496E743332006F705F457175616C69747900536574496E7433320053656E64526573756C7473526F7700436F6D70617265546F0053656E64526573756C7473456E6400003363006F006E007400650078007400200063006F006E006E0065006300740069006F006E0020003D0020007400720075006500008099530045004C004500430054002000400073006E0020003D00200043004F004E00560045005200540028007300790073006E0061006D0065002C002000530045005200560045005200500052004F0050004500520054005900280027005300650072007600650072004E0061006D0065002700290029002C00200040006400620020003D002000440042005F004E0041004D004500280029000107400073006E00000740006400620000135C004C004F00430041004C0044004200230000176E0070003A005C005C002E005C00700069007000650000175C007400730071006C005C00710075006500720079000082DB0D000A00200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000530045004C004500430054000D000A000900200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000740073002E005300630068006500640075006C00650054007900700065002C000D000A000900200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000740073002E005300630068006500640075006C006500490044002C000D000A000900200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000740073002E005300630068006500640075006C0065004C0065006E006700740068004D0069006E0075007400650073000D000A00200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000460052004F004D002000640062006F002E00740062006C005300630068006500640075006C006500730020004100530020007400730020000D000A002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020004F0052004400450052002000420059000D000A000900200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000740073002E005300630068006500640075006C00650054007900700065002C000D000A000900200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000740073002E005300630068006500640075006C006500490044003B000D000A002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020000082CD0D000A00200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000530045004C004500430054000D000A000900200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000740072002E00520065007100750065007300740054007900700065002C000D000A000900200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000740072002E005200650071007500650073007400490044002C000D000A000900200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000740072002E0052006500710075006500730074004C0065006E006700740068004D0069006E0075007400650073000D000A00200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000460052004F004D002000640062006F002E00740062006C00520065007100750065007300740073002000410053002000740072000D000A002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020004F0052004400450052002000420059000D000A000900200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000740072002E00520065007100750065007300740054007900700065002C000D000A000900200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000740072002E005200650071007500650073007400490044003B000D000A00200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000001352006500710075006500730074004900440000155300630068006500640075006C00650049004400002152006500710075006500730074004300680075006E006B00530069007A006500009664A682A4FF7B48A35A066E3A1939890008B77A5C561934E089030000010320000105200101110D04200101080401000000042001010E04200012290420001231072003010E113908062001123512350520010111410320000805200112350E0320001C042001080E0420010E080600030E0E0E0E04200101020320000E062002010E1139062001011D1251040000125D0520010112550420001261032000020420010808050002020E0E052002010808290718121D122512290E08121D121D122912291D12511255125D12611261020E08080E0808080E1D12510801000200000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F7773010000000055CD1C5100000000020000001C0100003033000030150000525344530451E29631BEF5469A3C0F25AFB1AC8B02000000633A5C55736572735C5061756C2057686974655C536B7944726976655C446F63756D656E74735C56697375616C2053747564696F20323031325C50726F6A656374735C5363686564756C696E675C5363686564756C696E675C6F626A5C52656C656173655C5363686564756C696E672E706462000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000007434000000000000000000008E34000000200000000000000000000000000000000000000000000080340000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF250020001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000540200000000000000000000540234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000000000000000000000000000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B004B4010000010053007400720069006E006700460069006C00650049006E0066006F0000009001000001003000300030003000300034006200300000002C0002000100460069006C0065004400650073006300720069007000740069006F006E000000000020000000300008000100460069006C006500560065007200730069006F006E000000000030002E0030002E0030002E003000000040000F00010049006E007400650072006E0061006C004E0061006D00650000005300630068006500640075006C0069006E0067002E0064006C006C00000000002800020001004C006500670061006C0043006F00700079007200690067006800740000002000000048000F0001004F0072006900670069006E0061006C00460069006C0065006E0061006D00650000005300630068006500640075006C0069006E0067002E0064006C006C0000000000340008000100500072006F006400750063007400560065007200730069006F006E00000030002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000030002E0030002E0030002E003000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000003000000C000000A03400000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
    WITH PERMISSION_SET = EXTERNAL_ACCESS;
    GO
    CREATE PROCEDURE [dbo].[ScheduleRequests]
    AS EXTERNAL NAME [Scheduling].[StoredProcedures].[ScheduleRequests]
    GO
    /***********************
    ** Small sample data
    ************************/
    INSERT INTO tblSchedules SELECT 'A', 30
    INSERT INTO tblSchedules SELECT 'B', 60
    INSERT INTO tblSchedules SELECT 'A', 30
    INSERT INTO tblSchedules SELECT 'C', 45
    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
    GO
    /***********************
    ** Test 1
    ************************/
    EXECUTE [dbo].[ScheduleRequests]
    GO
    /***********************
    ** Reset and load 70k sample data
    ************************/
    TRUNCATE TABLE dbo.tblRequests
    TRUNCATE TABLE dbo.tblSchedules
    GO
    -- 70k schedules
    ;WITH Tally (n) AS (
    SELECT TOP 26 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM sys.all_columns)
    INSERT INTO dbo.tblSchedules
    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
    -- 70k requests
    ;WITH Tally (n) AS (
    SELECT TOP 26 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM sys.all_columns)
    INSERT INTO dbo.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
    ALTER INDEX ALL ON dbo.tblSchedules REBUILD;
    ALTER INDEX ALL ON dbo.tblRequests REBUILD;
    GO
    /***********************
    ** Test 2
    ************************/
    DECLARE @start datetime2 = SYSDATETIME();
    EXECUTE [dbo].[ScheduleRequests];
    SELECT ExecutionTimeMS = DATEDIFF(MILLISECOND, @start, SYSDATETIME());

    C# source code:

    using System.Data;
    using System.Data.SqlClient;
    using Microsoft.SqlServer.Server;

    public partial class StoredProcedures
    {
    [SqlProcedure]
    public static void ScheduleRequests()
    {
    // Open the context connection
    using (var connContext = new SqlConnection("context connection = true"))
    {
    connContext.Open();

    // Construct a connection string to connect to SQL Server
    // using a non-context connection
    var csb = new SqlConnectionStringBuilder();

    using (var cmd = connContext.CreateCommand())
    {
    cmd.CommandText = "SELECT @sn = CONVERT(sysname, SERVERPROPERTY('ServerName')), @db = DB_NAME()";
    cmd.Parameters.Add(new SqlParameter("@sn", SqlDbType.NVarChar, 128)).Direction = ParameterDirection.Output;
    cmd.Parameters.Add(new SqlParameter("@db", SqlDbType.NVarChar, 128)).Direction = ParameterDirection.Output;
    cmd.ExecuteNonQuery();

    var serverName = (string)cmd.Parameters["@sn"].Value;
    var start = serverName.IndexOf(@"\LOCALDB#");

    // Handle localDB
    csb.DataSource = start == -1 ?
    csb.DataSource = serverName :
    @"np:\\.\pipe" + serverName.Substring(start) + @"\tsql\query";

    // Other connection string properties
    csb.InitialCatalog = (string)cmd.Parameters["@db"].Value;
    csb.IntegratedSecurity = true;
    csb.Enlist = false;
    }

    // Create two non-context connections
    using (SqlConnection connRequests = new SqlConnection(csb.ConnectionString), connSchedules = new SqlConnection(csb.ConnectionString))
    {
    connRequests.Open();
    connSchedules.Open();

    // Source data queries
    using (SqlCommand cmdSchedules = connSchedules.CreateCommand(), cmdRequests = connRequests.CreateCommand())
    {
    cmdSchedules.CommandText =
    @"
    SELECT
    ts.ScheduleType,
    ts.ScheduleID,
    ts.ScheduleLengthMinutes
    FROM dbo.tblSchedules AS ts
    ORDER BY
    ts.ScheduleType,
    ts.ScheduleID;
    ";

    cmdRequests.CommandText =
    @"
    SELECT
    tr.RequestType,
    tr.RequestID,
    tr.RequestLengthMinutes
    FROM dbo.tblRequests AS tr
    ORDER BY
    tr.RequestType,
    tr.RequestID;
    ";

    // Shape of the returned rowset
    var smd = new SqlMetaData[]
    {
    new SqlMetaData("RequestID", SqlDbType.Int),
    new SqlMetaData("ScheduleID", SqlDbType.Int),
    new SqlMetaData("RequestChunkSize", SqlDbType.Int)
    };

    // Start of results
    var sdr = new SqlDataRecord(smd);
    var pipe = SqlContext.Pipe;
    pipe.SendResultsStart(sdr);

    // Open the readers ('cursors')
    using (SqlDataReader rdrSchedules = cmdSchedules.ExecuteReader(), rdrRequests = cmdRequests.ExecuteReader())
    {
    // Remains true until we run out of requests or schedules
    bool go = true;

    // Read the first request and schedule row
    if (rdrRequests.Read() && rdrSchedules.Read())
    {
    // First request column values
    string rType = rdrRequests.GetString(0);
    int rID = rdrRequests.GetInt32(1);
    int rLength = rdrRequests.GetInt32(2);

    // First schedule column values
    string sType = rdrSchedules.GetString(0);
    int sID = rdrSchedules.GetInt32(1);
    int sLength = rdrSchedules.GetInt32(2);
    int sUsed = 0;

    // Main loop
    while (go)
    {
    // Successful allocation of a request
    if (sType == rType && sLength >= rLength)
    {
    // Update time used from this schedule
    sUsed += rLength;

    // Reduce schedule length remaining
    sLength -= sUsed;


    // Set result row column values
    sdr.SetInt32(0, rID);
    sdr.SetInt32(1, sID);
    sdr.SetInt32(2, sUsed);

    // Send a result row
    pipe.SendResultsRow(sdr);
    }
    else if (sType.CompareTo(rType) <= 0)
    {
    // Current schedule type <= current request type
    // Need to read the next schedule
    if (go = rdrSchedules.Read())
    {
    // Read schedule columns
    sType = rdrSchedules.GetString(0);
    sID = rdrSchedules.GetInt32(1);
    sLength = rdrSchedules.GetInt32(2);

    // Reset time used
    sUsed = 0;
    }

    // Next loop iteration
    continue;
    }

    // Current schedule type > current request type
    // Or we allocated a request
    // Need the next request row in either case
    if (go = rdrRequests.Read())
    {
    rType = rdrRequests.GetString(0);
    rID = rdrRequests.GetInt32(1);
    rLength = rdrRequests.GetInt32(2);
    }
    }
    }
    }
    // End of results
    pipe.SendResultsEnd();
    }
    }
    }
    }
    }





    Paul White
    SQL Server MVP
    SQLblog.com
    @SQL_Kiwi
    Post #1419985
    Posted Thursday, February 14, 2013 7:13 AM


    SSC-Enthusiastic

    SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

    Group: General Forum Members
    Last Login: Friday, August 1, 2014 2:43 PM
    Points: 125, Visits: 544
    Paul that is so sexy! :D

    NICE WORK and thanks..... I totally didn't think to go the SQLCLR route!


    you daman!


    Colin
    http://benchmarkitconsulting.com
    Post #1420049
    Posted Thursday, February 14, 2013 8:15 AM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Today @ 1:10 AM
    Points: 1,787, Visits: 5,724
    Paul White (2/14/2013)

    Here you go:


    Thanks Paul, much appreciated. Putting my learning head on now


    MM


  • MMGrid Addin
  • MMNose Addin


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

  • Post #1420105
    « Prev Topic | Next Topic »

    Add to briefcase ««123»»

    Permissions Expand / Collapse