February 25, 2010 at 9:50 am
Jack Corbett (2/25/2010)
Alvin,My organization uses Skype to Phone from international locations. It works okay as long as all that is going on is talking. If there is some type of support session going on the Skype to Phone gets worse. My understanding is that it is really based on the bandwidth available to the Skype user.
I'm just looking for a cheaper way of making calls to Canada. I've got a cheap VOIP service, with poor quality (delays, noise, echo), and I've had issues with bandwitdh before. Do not make calls at the same time as major file download. I don't use my "land line" much and I'm thinking about trying Skype. I guess I just need to give it a try too see how the quality is. Unfortunately the people I want to call do not use Skype.
Thanks Jack.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
February 25, 2010 at 10:01 am
Alvin Ramard (2/25/2010)
Jack Corbett (2/25/2010)
Alvin,My organization uses Skype to Phone from international locations. It works okay as long as all that is going on is talking. If there is some type of support session going on the Skype to Phone gets worse. My understanding is that it is really based on the bandwidth available to the Skype user.
I'm just looking for a cheaper way of making calls to Canada. I've got a cheap VOIP service, with poor quality (delays, noise, echo), and I've had issues with bandwitdh before. Do not make calls at the same time as major file download. I don't use my "land line" much and I'm thinking about trying Skype. I guess I just need to give it a try too see how the quality is. Unfortunately the people I want to call do not use Skype.
Thanks Jack.
What is your other service?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 25, 2010 at 10:11 am
CirquedeSQLeil (2/25/2010)
Alvin Ramard (2/25/2010)
Jack Corbett (2/25/2010)
Alvin,My organization uses Skype to Phone from international locations. It works okay as long as all that is going on is talking. If there is some type of support session going on the Skype to Phone gets worse. My understanding is that it is really based on the bandwidth available to the Skype user.
I'm just looking for a cheaper way of making calls to Canada. I've got a cheap VOIP service, with poor quality (delays, noise, echo), and I've had issues with bandwitdh before. Do not make calls at the same time as major file download. I don't use my "land line" much and I'm thinking about trying Skype. I guess I just need to give it a try too see how the quality is. Unfortunately the people I want to call do not use Skype.
Thanks Jack.
What is your other service?
VOIP.com
I'm paying around $15/month (includes all taxes and fees) for 300 minutes. I use less than 10% of that.
I just had an idea that maybe I should contact them about hardware. I signed up with them when my previous service provider suddenly quit. There was such a demand on hardware when that happened that I had to wait 3 weeks for the hardware.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
February 25, 2010 at 10:15 am
Alvin Ramard (2/25/2010)
VOIP.com
I'm paying around $15/month (includes all taxes and fees) for 300 minutes. I use less than 10% of that.
I just had an idea that maybe I should contact them about hardware. I signed up with them when my previous service provider suddenly quit. There was such a demand on hardware when that happened that I had to wait 3 weeks for the hardware.
I use vonage. I think I need to contact them about hardware as well. I have been using the same device for about 5 years. It is really nice because I have moved a couple of times to different cities as well as out of state. I get to keep the same phone number and family from home still get to make a local phone call to call us.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 25, 2010 at 10:40 am
I used to have Vonage as well, and it worked fairly well for me. As with any such thing, bandwidth was my issue, no large file downloads, or at least no file downloads with accelerator applications while talking on the phone.
I currently use Comcast's service as it ended up being cheaper in the long run, or at least for a few more months until I switch everything to Fios. So far I can't complain about their service either. For the most part it just works.
-Luke.
February 25, 2010 at 11:22 am
I've used Vonage since 2007 and have been happy with it. My wife changed our plan on it and her calls home to Venezuela are either free or dirt cheap. She does the bills so I don't really know anymore. 😀
---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum
February 25, 2010 at 2:17 pm
The Thread is very quite today.. Either the Posted questions are getting better or the Answers are getting better. I wonder which...
-Roy
February 25, 2010 at 2:25 pm
Roy, we're just resting for tomorrow and the weekend. 😀
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
February 25, 2010 at 3:20 pm
I won't be hanging around The Thread during the day Saturday and Sunday. From the looks of it, I may be freezing up and down the pitch those days as I officiate at the Sunbelt Tournament. Dang it, it was supposed to be nice this weekend.
February 25, 2010 at 5:45 pm
Alvin Ramard (2/25/2010)
Is it just me or is it getting a bit thechnical in here? 😎
It was for a bit, yes. Happy to talk about phone-related things now - mine is attached to the wall by a short length of copper wire 🙂
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 25, 2010 at 8:50 pm
I've just received permission from Itzik to share my implementation with you, so I can finally post the test rig from his Calculating Concurrent Sessions article and my CLR UDA solution. This is 2008-only code and not production-quality.
In particular, the production version will not throw an exception if a row arrives out of sequence: it will optimize for rows received in sequence, but degrade gracefully (using a caching mechanism) if this optimization doesn't hold.
Test rig
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;
GO
C# 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());
}
}
}
I apologise for the technical content 🙂
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 26, 2010 at 8:48 am
I'll make up for Paul's inadvertant technical stuff 😉
Steve, saw you were re-learning Pythagorean theorem, can't help but poison your mind with a joke:
An Indian chief had three wives who were preparing to give birth, one on a buffalo hide, one on a bear hide and the third on a hippopotamus hide. In due course, the first gave him a son, the second a daughter, and the third, twins, a boy and a girl, thereby illustrating the well known theorem that the squaw on the hippopotamus is equal to the sum of the squaws on the other two hides.
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
February 26, 2010 at 9:16 am
Execute DBCC TimeWarp @Param137 = 'Post #873495', @Action = 'Erase for all eternity'
😎
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
February 26, 2010 at 9:28 am
Alvin Ramard (2/26/2010)
Execute DBCC TimeWarp @Param137 = 'Post #873495', @Action = 'Erase for all eternity'
😎
Thanks - i needed that wiped clean 😉
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 26, 2010 at 9:48 am
jcrawf02 (2/26/2010)
I'll make up for Paul's inadvertant technical stuff 😉Steve, saw you were re-learning Pythagorean theorem, can't help but poison your mind with a joke:
An Indian chief had three wives who were preparing to give birth, one on a buffalo hide, one on a bear hide and the third on a hippopotamus hide. In due course, the first gave him a son, the second a daughter, and the third, twins, a boy and a girl, thereby illustrating the well known theorem that the squaw on the hippopotamus is equal to the sum of the squaws on the other two hides.
And to think, you'll get away with that joke because of the translation. Originally (and currently, in the base language), "squaw" had the same pejorative connotations of calling a woman the four-letter English word that starts with "c" and refers to her reproductive organs.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 15 posts - 12,076 through 12,090 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply