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
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi