February 13, 2013 at 10:28 pm
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
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 14, 2013 at 2:02 am
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
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);February 14, 2013 at 4:55 am
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
February 14, 2013 at 7:13 am
Paul that is so sexy! 😀
NICE WORK and thanks..... I totally didn't think to go the SQLCLR route!
you daman!
February 14, 2013 at 8:15 am
Paul White (2/14/2013)
Here you go:
Thanks Paul, much appreciated. Putting my learning head on now 🙂
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);February 14, 2013 at 8:32 am
Mister Magoo, that is some very slick TSQL.
Paul White - yet again you have impressed!! Very few people out there appreciate how powerful SQLCLR can be (when done correctly) for some classes of problems!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 14, 2013 at 8:51 am
TheSQLGuru (2/14/2013)
Mister Magoo, that is some very slick TSQL.
Thanks.
Paul White - yet again you have impressed!! Very few people out there appreciate how powerful SQLCLR can be (when done correctly) for some classes of problems!
I know, that CLR is so simple and elegant! I thought I needed my learning head, but what I really needed was my "smack yourself in the face for not spotting the simplicity of the problem", so much that at first, I thought that packing by type then ID was wrong...until my brain caught up to the fact that each type can be packed separately - as Paul has done so well!
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);February 14, 2013 at 1:58 pm
The C# code could no doubt be improved - I'm not an expert in that language by any means, but it does bear a resemblance to the sort of code one might write if looking to solve this problem with a T-SQL cursor.
It's curious to me that T-SQL cursors perform quite so badly as they do. Imagine the performance of T-SQL cursors that were optimized for this sort of iterative processing, rather than issuing a separate query per row, requiring storage in T-SQL variables and so on. There's no way it should be thousands of times faster to create two new database connections and stream rows to and from .NET code, compared with native T-SQL cursor processing.
I believe other engines implement cursors quite differently, with much better performance. I understand this performance difference is a big cause of puzzlement for people migrating to SQL Server from those products. The same sort of argument applies to T-SQL scalar functions; these are deeply evil in SQL Server, but if you think about it there's no real reason that should be the case, if implemented differently. Nevertheless, this is where we find ourselves today.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 14, 2013 at 2:32 pm
Paul White (2/14/2013)
The C# code could no doubt be improved - I'm not an expert in that language by any means, but it does bear a resemblance to the sort of code one might write if looking to solve this problem with a T-SQL cursor.It's curious to me that T-SQL cursors perform quite so badly as they do. Imagine the performance of T-SQL cursors that were optimized for this sort of iterative processing, rather than issuing a separate query per row, requiring storage in T-SQL variables and so on. There's no way it should be thousands of times faster to create two new database connections and stream rows to and from .NET code, compared with native T-SQL cursor processing.
I believe other engines implement cursors quite differently, with much better performance. I understand this performance difference is a big cause of puzzlement for people migrating to SQL Server from those products. The same sort of argument applies to T-SQL scalar functions; these are deeply evil in SQL Server, but if you think about it there's no real reason that should be the case, if implemented differently. Nevertheless, this is where we find ourselves today.
+1000 on all counts - well, maybe only +13.74 on the C# code maybe not being optimal :hehe:
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 15, 2013 at 5:27 am
I believe other engines implement cursors quite differently, with much better performance
In Oracle they are considered by many to be best practice..
I just detest the interface...!
Thanks for the code Paul
Andy
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply