January 25, 2012 at 7:26 pm
So I have inherited a stored proc that needs some sprucing up. The developer had created it as I will show below, but I think using quirky update may be a better solution. However, I am not sure.
Case: We would like to know, for a specific customerId, what the highest amount of open sessions was at any one time throughout a given range of time.
When this range begins, some people may already be logged in while others will log in during this range. Some who were already logged in will log out, or stay logged in while those who log in during the range have the same options. At any singular time point during this range n amount of people are logged in and we want to find what n is when it is highest.
Current stored proc does the following:
1. inserts into a temp table all individual logins that began during this range with the login time and the integer 1
2. inserts into the same table all individual logins that ended during this range with the logout time and the integer -1
3. gets a count of all logins that began before the start of the range that had not logged out before the start of the range
4. set a marker equal to this count
5. use cursor to move marker up or down by stepping through a running tally of the temp table ordered by login/logout time
6. return the marker
Sample code:
USE tempdb
--Create table mimicing production data
CREATE TABLE #logins
(loginRec int identity(1,1) primary key
, customerId int
, loginTime datetime
, logoutTime datetime)
--Insert sample data
INSERT INTO #logins
SELECT 1352, '2012-01-25 07:50:00.000', NULL
UNION ALL
SELECT 1352, '2012-01-25 07:55:00.000', '2012-01-25 07:56:00.000'
UNION ALL
SELECT 1352, '2012-01-25 07:58:00.000', '2012-01-25 08:10:00.000'
UNION ALL
SELECT 1352, '2012-01-25 08:05:00.000', NULL
UNION ALL
SELECT 1352, '2012-01-25 08:07:00.000', '2012-01-25 08:08:00.000'
UNION ALL
SELECT 1352, '2012-01-25 08:07:00.000', '2012-01-25 09:07:00.000'
UNION ALL
SELECT 1352, '2012-01-25 08:09:00.000', '2012-01-25 08:15:00.000'
UNION ALL
SELECT 1352, '2012-01-25 08:10:00.000', '2012-01-25 08:14:00.000'
UNION ALL
SELECT 1352, '2012-01-25 08:45:00.000', '2012-01-25 08:50:00.000'
UNION ALL
SELECT 1352, '2012-01-25 08:55:00.000', NULL
UNION ALL
SELECT 1352, '2012-01-25 09:02:00.000', NULL
--declare parameters that would be passed to stored proc
DECLARE @beginRange datetime = '2012-01-25 08:00:00.000'
DECLARE @endRange datetime = '2012-01-25 09:00:00.000'
/*****************************
Where stored proc would begin
******************************/
--temp table to hold unpivoted data
CREATE TABLE #count
(logTime datetime
, sumKey INT)
--one record for each login that began within range
--set sumKey = 1
INSERT INTO #count
SELECT loginTime, 1
FROM #logins
WHERE loginTime >= @beginRange
AND loginTime < @endRange
AND customerId = 1352
--one record for each login that ended within range
--set sumKey = -1
INSERT INTO #count
SELECT logoutTime, -1
FROM #logins
WHERE logoutTime >= @beginRange
AND logoutTime < @endRange
AND customerId = 1352
DECLARE @minCount INT --declare our staring counter
DECLARE @highWater INT --declare our highest concurrent logins counter
--initialize our starting counter with count of logins
--that happened before range that are still logged in
--or logged out within the range
SELECT @minCount = COUNT(*)
FROM #logins
WHERE loginTime < @beginRange
AND (logoutTime IS NULL OR logoutTime >= @beginRange)
AND customerId = 1352;
--set highWater eual to the minimum
SELECT @highWater = @minCount;
--cursor to add sumKey in order of time and move highWater counter
DECLARE @incr INT
DECLARE cur CURSOR FOR
SELECT sumKey
FROM #count
ORDER BY logTime;
OPEN cur;
FETCH cur INTO @incr;
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @minCount = @minCount + @incr;
IF @minCount > @highWater
SELECT @highWater = @minCount;
FETCH cur INTO @incr;
END
CLOSE cur;
DEALLOCATE cur;
--return highWater or our highest amount of concurrent
--logins at any point within the range
SELECT @highWater;
--clean up
DROP TABLE #logins;
DROP TABLE #count;
Thoughts on how to simplify and improve on performance? I am thinking to use quirky update on the temp table.
Jared
CE - Microsoft
January 25, 2012 at 8:22 pm
Itzik Ben-Gan ran a series of articles exploring T-SQL solutions to this problem:
http://www.sqlmag.com/article/tsql3/calculating-concurrent-sessions-part-3
The link above gives a very neat solution provided by several people including a past regular contributor to SSC, R Barry Young. I also have a CLR solution which is 20-30% faster even than that, I include the code below in case it is useful to you.
USE tempdb;
SET NOCOUNT ON;
SET ROWCOUNT 0;
GO
-- Conditional test object drops
IF OBJECT_ID('dbo.GetNums', 'IF') IS NOT NULL DROP FUNCTION dbo.GetNums;
IF OBJECT_ID('dbo.Sessions', 'U') IS NOT NULL DROP TABLE dbo.Sessions;
IF EXISTS (SELECT * FROM sys.objects WHERE name = N'MAX_ConcurrentSessions' AND [schema_id] = SCHEMA_ID(N'dbo')) DROP AGGREGATE dbo.MAX_ConcurrentSessions;
IF EXISTS (SELECT * FROM sys.assemblies WHERE name = N'CLR') DROP ASSEMBLY CLR;
GO
-- Ensure CLR is enabled
IF NOT EXISTS (SELECT * FROM sys.configurations WHERE name = 'clr enabled' AND value_in_use = 1)
BEGIN
EXECUTE sys.sp_configure 'clr enabled', 1;
RECONFIGURE;
END;
GO
CREATE ASSEMBLY CLR
AUTHORIZATION dbo
--FROM N'C:\Documents and Settings\Paul\My Documents\Visual Studio 2008\Projects\CLR\CLR\bin\Release\CLR.dll'
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C0103005554864B0000000000000000E00002210B0108000010000000060000000000000E2E0000002000000040000000004000002000000002000004000000000000000400000000000000008000000002000000000000030040850000100000100000000010000010000000000000100000000000000000000000C02D00004B00000000400000D802000000000000000000000000000000000000006000000C000000282D00001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000140E0000002000000010000000020000000000000000000000000000200000602E72737263000000D8020000004000000004000000120000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001600000000000000000000000000004000004200000000000000000000000000000000F02D000000000000480000000200050054220000D40A00000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000133003002F00000001000011021F40731300000A7D01000004027E1400000A0A1200281500000A7D0200000402167D0300000402167D040000042A0013300400EA000000020000110F01281600000A2D090F02281600000A2C0B7201000070731700000A7A0F01281800000A0C1202281500000A0A06027B02000004303672330000700F01FE16040000016F1900000A027B02000004731A00000A0D1203FE161D0000016F1900000A281B00000A731700000A7A027B010000040F02281800000A13041204281500000A6F1C00000A02257B0300000417587D03000004160B2B2D027B01000004076F1D00000A062F1A02257B0300000417597D03000004027B01000004076F1E00000A0717580B07027B0300000432CA027B03000004027B04000004310C02027B030000047D040000042A2E7299000070731F00000A7A32027B04000004732000000A2A0013300300480000000300001103027B020000046F2100000A03027B040000046F2200000A03027B030000046F2200000A160A2B1603027B01000004066F1D00000A6F2100000A0617580A06027B0300000432E12A13300300580000000300001102036F2300000A7D0200000402036F2400000A7D0400000402036F2400000A7D0300000402027B03000004731300000A7D01000004160A2B15027B01000004036F2300000A6F1C00000A0617580A06027B0300000432E22A42534A4201000100000000000C00000076322E302E35303732370000000005006C0000003C030000237E0000A80300006404000023537472696E6773000000000C08000010010000235553001C0900001000000023475549440000002C090000A801000023426C6F620000000000000002000001571702080900000000FA01330016000001000000210000000200000004000000060000000500000001000000240000000E0000000300000001000000010000000200000000000A000100000000000600390032000A006A004F00060096007B000A00F300DE000A001001DE0006002D0123010600400123010600880176010600A50176010600C20176010600E10176010600FA01760106001302760106002E0276010600490276010600810262020600950262020600A30276010600BC0276010600EC02D90253000003000006002F030F0306004F030F030600710332000A0087034F000A00A8034F000600AF0362020600C50362020600D00332000600F703320006000B04320006001B0432000600380432000000000001000000000001000100092110001200000005000100010001009D000A000100AE0011000100BD0014000100CD0014005020000000008600D900170001008C20000000008600FF001B00010082210000000086000A01230003008E210000000086001901290004009C2100000000E6013A012E000400F02100000000E6014D0134000500000001005201000002006001000001006C010000010072010000010074010200090041009F013A0049009F013A0051009F013A0059009F013A0061009F013A0069009F013A0071009F013A0079009F013A0081009F013F0089009F013A0091009F013A0099009F013A00A1009F014400B1009F014A00B9009F011700C1009F011700C9009F014F00D9009F01C8000C009F014A00E900D903D400E900E203D8002100EC03E100F1009F013A0021000104E500F9001204EA00E9009F01EE000101A803F3000C002204FA000C00260400010C002F044A0009019F013A0029009F014A0031003A01EE0031003A014A0039005004D80039005A0415012E00330028012E000B0019012E00130022012E001B0022012E00230022012E002B0019012E00530040012E003B0022012E004B0022012E006B0077012E007B0089012E0063006A012E007300800143008B005500DC0006011101CE000480000001000000000000000000000000006D0300000200000000000000000000000100290000000000020000000000000000000000010043000000000000000000003C4D6F64756C653E00434C522E646C6C004D41585F436F6E63757272656E7453657373696F6E73006D73636F726C69620053797374656D0056616C7565547970650053797374656D2E44617461004D6963726F736F66742E53716C5365727665722E536572766572004942696E61727953657269616C697A650053797374656D2E436F6C6C656374696F6E732E47656E65726963004C697374603100656E644461746554696D654361636865006C61737453746172745469636B730063757272656E7453657373696F6E73006D617853657373696F6E7300496E69740053797374656D2E446174612E53716C54797065730053716C4461746554696D6500416363756D756C617465004D657267650053716C496E743332005465726D696E6174650053797374656D2E494F0042696E6172795772697465720057726974650042696E61727952656164657200526561640053746172744461746554696D6500456E644461746554696D65006F74686572007700720053797374656D2E5265666C656374696F6E00417373656D626C795469746C65417474726962757465002E63746F7200417373656D626C794465736372697074696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C747572654174747269627574650053797374656D2E52756E74696D652E496E7465726F70536572766963657300436F6D56697369626C65417474726962757465004775696441747472696275746500417373656D626C7956657273696F6E41747472696275746500417373656D626C7946696C6556657273696F6E4174747269627574650053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C69747941747472696275746500434C520053657269616C697A61626C654174747269627574650053716C55736572446566696E656441676772656761746541747472696275746500466F726D6174005374727563744C61796F7574417474726962757465004C61796F75744B696E64004461746554696D65004D696E56616C7565006765745F5469636B73006765745F49734E756C6C00457863657074696F6E006765745F56616C7565004F626A65637400546F537472696E6700537472696E6700416464006765745F4974656D0052656D6F76654174004E6F74496D706C656D656E746564457863657074696F6E0052656164496E7436340052656164496E7433320000314E0055004C004C007300200061007200650020006E006F00740020007000650072006D00690074007400650064002E00006552006F00770020007200650063006500690076006500640020006F007500740020006F00660020006F0072006400650072003A0020007200650063006500690076006500640020007B0030007D0020006100660074006500720020007B0031007D002E0000755000610072007400690061006C0020006100670067007200650067006100740069006F006E007300200061006E006400200070006100720061006C006C0065006C00690073006D0020006E006F007400200073007500700070006F007200740065006400200028004D00650072006700650029000000AB1FBC2284689F43B406487088E98A490008B77A5C561934E089060615120D010A02060A020608032000010720020111111111052001011108042000111505200101121905200101121D042001010E0420010102052001011155042001010805200101116972010002000000050054020D49734E756C6C4966456D707479015402124973496E76617269616E74546F4E756C6C73005402174973496E76617269616E74546F4475706C696361746573005402124973496E76617269616E74546F4F726465720054080B4D61784279746553697A65FFFFFFFF0520010111710515120D010A030611750320000A04070111750320000204200011750320000E042001010A0600030E0E1C1C0520010113000520011300080A07050A08117511751175030701080320000808010003434C52000005010000000017010012436F7079726967687420C2A920203230313000002901002432313839376237362D666135652D343364632D386134392D65633639623936336561393700000C010007312E302E302E3000000801000200000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F777301000000005554864B00000000020000007C000000442D0000440F0000525344539FE1F1A79E656C45AD0851FB14541F1C0B000000433A5C446F63756D656E747320616E642053657474696E67735C5061756C5C4D7920446F63756D656E74735C56697375616C2053747564696F20323030385C50726F6A656374735C434C525C434C525C6F626A5C52656C656173655C434C522E70646200E82D00000000000000000000FE2D0000002000000000000000000000000000000000000000000000F02D00000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF250020400000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000800200000000000000000000800234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000001000000000000000100000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B004E0010000010053007400720069006E006700460069006C00650049006E0066006F000000BC0100000100300030003000300030003400620030000000300004000100460069006C0065004400650073006300720069007000740069006F006E000000000043004C0052000000300008000100460069006C006500560065007200730069006F006E000000000031002E0030002E0030002E003000000030000800010049006E007400650072006E0061006C004E0061006D006500000043004C0052002E0064006C006C0000004800120001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020002000320030003100300000003800080001004F0072006900670069006E0061006C00460069006C0065006E0061006D006500000043004C0052002E0064006C006C000000280004000100500072006F0064007500630074004E0061006D0065000000000043004C0052000000340008000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0030002E00300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000103E00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = SAFE;
GO
CREATE AGGREGATE dbo.MAX_ConcurrentSessions
(
@StartDate DATETIME,
@EndDate DATETIME
)
RETURNS INTEGER
EXTERNAL NAME CLR.MAX_ConcurrentSessions;
GO
-- The dbo.GetNums function from the article 'Calculating Concurrent Sessions'
CREATE FUNCTION dbo.GetNums(@n AS BIGINT)
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN
WITH
L0 AS (SELECT 1 AS C UNION ALL SELECT 1),
L1 AS (SELECT 1 AS C FROM L0 AS A CROSS JOIN L0 AS B),
L2 AS (SELECT 1 AS C FROM L1 AS A CROSS JOIN L1 AS B),
L3 AS (SELECT 1 AS C FROM L2 AS A CROSS JOIN L2 AS B),
L4 AS (SELECT 1 AS C FROM L3 AS A CROSS JOIN L3 AS B),
L5 AS (SELECT 1 AS C FROM L4 AS A CROSS JOIN L4 AS B),
NUM AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS n FROM L5)
SELECT TOP(@n) n FROM NUM
ORDER BY n;
GO
-- The dbo.Sessions table from the article 'Calculating Concurrent Sessions'
CREATE TABLE dbo.Sessions
(
keycol INTEGER CONSTRAINT PK__dbo_Sessions
PRIMARY KEY CLUSTERED,
app VARCHAR(10) NOT NULL,
usr VARCHAR(10) NOT NULL,
host VARCHAR(10) NOT NULL,
starttime DATETIME NOT NULL,
endtime DATETIME NOT NULL,
CHECK(endtime > starttime)
);
GO
/********************
** TEST DATA ROWS **
********************/
DECLARE @NumRows AS INTEGER;
SET @NumRows = 2500000;
RAISERROR('Creating %i rows of test data...', 0, 1, @NumRows) WITH NOWAIT;
INSERT dbo.Sessions WITH (TABLOCK)
(keycol, app, usr, host, starttime, endtime)
SELECT keycol = ROW_NUMBER() OVER (ORDER BY (SELECT 0)),
D.app,
D.usr,
D.host,
D.starttime,
endtime = DATEADD(SECOND, 1 + ABS(CHECKSUM(NEWID())) % (20 * 60), starttime)
FROM (
SELECT app = 'app' + CAST(1 + ABS(CHECKSUM(NEWID())) % 10 AS VARCHAR(10)),
usr ='user1',
host = 'host1',
starttime = DATEADD(SECOND, 1 + ABS(CHECKSUM(NEWID())) % (30 * 24 * 60 * 60), '20090101')
FROM dbo.GetNums(@NumRows) AS NUM
WHERE n <= @NumRows
)
AS D;
GO
RAISERROR('Creating index 1 of 2...', 0, 1) WITH NOWAIT;
GO
-- Index required for both the T-SQL and CLR methods
CREATE INDEX idx_nc_app_st_et
ON dbo.Sessions
(app ASC, starttime ASC, endtime ASC)
WITH (MAXDOP = 1, SORT_IN_TEMPDB = ON, ONLINE = OFF, FILLFACTOR = 100);
GO
RAISERROR('Creating index 2 of 2...', 0, 1) WITH NOWAIT;
GO
-- Index required for the T-SQL solution only
CREATE INDEX idx_nc_app_et_st
ON dbo.Sessions
(app ASC, endtime ASC, starttime ASC)
WITH (MAXDOP = 1, SORT_IN_TEMPDB = ON, ONLINE = OFF, FILLFACTOR = 100);
GO
-- Ensure the CLR routine is fully compiled
DECLARE @dummy INTEGER;
SELECT @dummy = dbo.MAX_ConcurrentSessions('19000101', '19000101');
WAITFOR DELAY '00:00:01';
GO
/**************************
** TEST RUN START POINT **
**************************/
-----------------
-- T-SQL solution
-----------------
DECLARE @TestStart DATETIME,
@TestRuns TINYINT,
@TSQLTime INTEGER,
@CLRTime INTEGER,
@CurrentRun TINYINT,
@AppBitBucket VARCHAR(10),
@MXbitBucket INTEGER;
/********************
** TEST RUN COUNT **
********************/
SET @TestRuns = 10;
SET @CurrentRun = 1;
SET @TestStart = CURRENT_TIMESTAMP;
WHILE (@CurrentRun <= @TestRuns)
BEGIN
RAISERROR('Running T-SQL solution test %i of %i...', 0, 1, @CurrentRun, @TestRuns) WITH NOWAIT;
IF (@CurrentRun = 1)
BEGIN
-- Output results for comparison on run #1 only
WITH C1
AS (
SELECT app,
ts = starttime,
type = +1,
start_ordinal = ROW_NUMBER() OVER (PARTITION BY app ORDER BY starttime ASC)
FROM dbo.Sessions
UNION ALL
SELECT app,
endtime,
-1,
NULL
FROM dbo.Sessions
),
C2
AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY app ORDER BY ts ASC, type ASC) AS start_or_end_ordinal
FROM C1
)
SELECT C2.app,
mx = MAX(2 * C2.start_ordinal - C2.start_or_end_ordinal)
FROM C2
WHERE C2.type = 1
GROUP BY C2.app
OPTION (MAXDOP 1);
END
ELSE
BEGIN
-- Output results for comparison on run #1 only
WITH C1
AS (
SELECT app,
ts = starttime,
type = +1,
start_ordinal = ROW_NUMBER() OVER (PARTITION BY app ORDER BY starttime ASC)
FROM dbo.Sessions
UNION ALL
SELECT app,
endtime,
-1,
NULL
FROM dbo.Sessions
),
C2
AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY app ORDER BY ts ASC, type ASC) AS start_or_end_ordinal
FROM C1
)
SELECT @AppBitBucket = C2.app,
@MXbitBucket = MAX(2 * C2.start_ordinal - C2.start_or_end_ordinal)
FROM C2
WHERE C2.type = 1
GROUP BY C2.app
OPTION (MAXDOP 1);
END;
SET @CurrentRun = @CurrentRun + 1;
END;
SET @TSQLTime = DATEDIFF(MILLISECOND, @TestStart, CURRENT_TIMESTAMP) / @TestRuns;
SELECT test_runs = @TestRuns,
[T-SQL Average Execution Time (ms)] = @TSQLTime;
RAISERROR('T-SQL Average Execution Time (ms) = %i', 0, 1, @TSQLTime) WITH NOWAIT;
---------------
-- CLR solution
---------------
SET @CurrentRun = 1;
SET @TestStart = CURRENT_TIMESTAMP;
WHILE (@CurrentRun <= @TestRuns)
BEGIN
RAISERROR('Running CLR solution test %i of %i...', 0, 1, @CurrentRun, @TestRuns) WITH NOWAIT;
IF (@CurrentRun = 1)
BEGIN
WITH Sorted
AS (
SELECT TOP (9223372036854775807)
app,
starttime,
endtime
FROM dbo.Sessions
ORDER BY
app, starttime
)
SELECT S.app,
mx = dbo.MAX_ConcurrentSessions(S.starttime, S.endtime)
FROM Sorted S
GROUP BY S.app
OPTION (MAXDOP 1);
END
ELSE
BEGIN
WITH Sorted
AS (
SELECT TOP (9223372036854775807)
app,
starttime,
endtime
FROM dbo.Sessions
ORDER BY
app, starttime
)
SELECT @AppBitBucket= S.app,
@MXbitBucket = dbo.MAX_ConcurrentSessions(S.starttime, S.endtime)
FROM Sorted S
GROUP BY S.app
OPTION (MAXDOP 1);
END;
SET @CurrentRun = @CurrentRun + 1;
END;
SET @CLRTime = DATEDIFF(MILLISECOND, @TestStart, CURRENT_TIMESTAMP) / @TestRuns;
SELECT test_runs = @TestRuns,
[CLR Average Execution Time (ms)] = @CLRTime;
RAISERROR('CLR Average Execution Time (ms) = %i', 0, 1, @CLRTime) WITH NOWAIT;
DECLARE @Difference INTEGER;
SET @Difference = (@TSQLTime - @CLRTime ) * 100 / @TSQLTime;
IF @Difference < 0
BEGIN
RAISERROR('T-SQL faster by %03i%%', 0, 1, @Difference) WITH NOWAIT;
END
ELSE
BEGIN
SET @Difference = - @Difference;
RAISERROR('CLR faster by %03i%%', 0, 1, @Difference) WITH NOWAIT;
END;
Source code:
using System;
using System.Collections.Generic;
using System.Data.SqlTypes;
using System.IO;
using Microsoft.SqlServer.Server;
[Serializable]
[SqlUserDefinedAggregate
(
Format.UserDefined, // Custom serialization required
IsNullIfEmpty = true, // Returns NULL if no values have been aggregated
IsInvariantToNulls = false, // NULLs do affect the aggregation
IsInvariantToDuplicates = false, // Duplicate values do affect the aggregation
IsInvariantToOrder = false, // Order of rows presented matters (not implemented in 2008)
MaxByteSize = -1 // Maximum size of the custom serialized representation
)
]
public struct MAX_ConcurrentSessions : IBinarySerialize
{
private List<long> endDateTimeCache; // Cache of end datetimes
private long lastStartTicks; // The last start datetime received, in ticks
private int currentSessions; // Number of concurrent sessions after each row
private int maxSessions; // Maximum number of concurrent sessions encountered
// Called by SQL Server to reset this instance
public void Init()
{
// Create the cache, with room for 64 items
endDateTimeCache = new List<long>(64);
// Smallest possible value for the last start datetime received (in ticks)
lastStartTicks = DateTime.MinValue.Ticks;
// No current sessions
currentSessions = 0;
// Reset the running aggregate
maxSessions = 0;
}
public void Accumulate(SqlDateTime StartDateTime, SqlDateTime EndDateTime)
{
// NULL parameters are not allowed
if (StartDateTime.IsNull || EndDateTime.IsNull)
{
throw new Exception("NULLs are not permitted.");
}
// Get the start datetime as a number of ticks
long startTicks = StartDateTime.Value.Ticks;
// Check the row was received in the expected order (start datetime order)
if (startTicks <= this.lastStartTicks)
{
throw new Exception
(
String.Format
(
"Row received out of order: received {0} after {1}.",
StartDateTime.ToString(),
new DateTime(this.lastStartTicks).ToString()
)
);
}
// Add the end datetime to the cache (it can't possibly have been reached yet)
this.endDateTimeCache.Add(EndDateTime.Value.Ticks);
// A new session started
this.currentSessions++;
// Search for any end datetimes in the cache which have been passed
// by this row's start datetime. The number of items in cache
// is equal to the value of currentSessions
for (int i = 0; i < currentSessions; i++)
{
// Test this cached item
if (this.endDateTimeCache < startTicks)
{
// Reduce concurrent sessions by one
this.currentSessions--;
// Remove the session end date from the cache
this.endDateTimeCache.RemoveAt(i);
}
}
// Check if we need to adjust the maximum concurrent sessions counter
if (this.currentSessions > this.maxSessions)
{
this.maxSessions = this.currentSessions;
}
}
// Called by SQL Server to merge partial aggregations
public void Merge(MAX_ConcurrentSessions other)
{
throw new NotImplementedException("Partial aggregations and parallelism not supported (Merge)");
}
// Called by SQL Server to obatin the result, once all rows have been
// processed through the Accumulate() method
public SqlInt32 Terminate()
{
// Just return the running maximum
return new SqlInt32(maxSessions);
}
// Called when SQL Server needs to serialize this instance
public void Write(BinaryWriter w)
{
// Write fields
w.Write(this.lastStartTicks);
w.Write(this.maxSessions);
w.Write(this.currentSessions);
// Write the cache
for (int i = 0; i < this.currentSessions; i++)
{
w.Write(this.endDateTimeCache);
}
}
// Called when SQL Server needs to deserialize this instance
public void Read(BinaryReader r)
{
// Read fields
this.lastStartTicks = r.ReadInt64();
this.maxSessions = r.ReadInt32();
this.currentSessions = r.ReadInt32();
// Create the cache
this.endDateTimeCache = new List<long>(this.currentSessions);
// Populate the cache
for (int i = 0; i < this.currentSessions; i++)
{
this.endDateTimeCache.Add(r.ReadInt64());
}
}
}
January 25, 2012 at 9:10 pm
Thanks Paul! I'll take a closer look tomorrow morning.
Jared
CE - Microsoft
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply