Puzzle / CTE Help - NO CURORS :)

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Puzzle / CTE Help - NO CURORS :)

25 posts, Page 2 of 3

««1 2 3 »»

Puzzle / CTE Help - NO CURORS :)

Rate Topic

Display Mode

Topic Options

Author

Message

mister.magoo

Posted Tuesday, February 12, 2013 4:46 PM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 4:46 PM
Points: 1,287, Visits: 3,850

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

Post #1419237

Jeff Moden

Posted Tuesday, February 12, 2013 8:46 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 6:33 PM
Points: 32,889, Visits: 26,757

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

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1419282

dwain.c

Posted Tuesday, February 12, 2013 10:12 PM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 12:42 AM
Points: 2,338, Visits: 3,158

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.

No loops! No CURSORs! No RBAR! Hoo-uh!

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?

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Are you too recursively challenged?
Splitting strings based on patterns can be fast!

Post #1419289

mister.magoo

Posted Wednesday, February 13, 2013 5:43 PM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 4:46 PM
Points: 1,287, Visits: 3,850

I knew I had rushed it earlier Wink

- 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

Post #1419781

dwain.c

Posted Wednesday, February 13, 2013 6:09 PM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 12:42 AM
Points: 2,338, Visits: 3,158

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.

Post #1419785

Paul White

Posted Wednesday, February 13, 2013 10:28 PM

SSChampion

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 10:33 AM
Points: 10,989, Visits: 10,529

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 Smile

Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi

Post #1419814

mister.magoo

Posted Thursday, February 14, 2013 2:02 AM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 4:46 PM
Points: 1,287, Visits: 3,850

Paul White (2/13/2013)

mister.magoo (2/13/2013)

this version packs 70,000 requests in about 13 seconds on my desktop.

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 BigGrin

Post #1419875

Paul White

Posted Thursday, February 14, 2013 4:55 AM

SSChampion

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 10:33 AM
Points: 10,989, Visits: 10,529

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

Colin Stasiuk

Posted Thursday, February 14, 2013 7:13 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, February 14, 2013 2:04 PM
Points: 125, Visits: 485

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

mister.magoo

Posted Thursday, February 14, 2013 8:15 AM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 4:46 PM
Points: 1,287, Visits: 3,850

Paul White (2/14/2013)

Here you go:

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

Post #1420105

« Prev Topic | Next Topic »

25 posts, Page 2 of 3

««1 2 3 »»

Permissions