Simultaneous calls

Question

I am trying to calculate the number of simultaneous calls at a time when a particular call is being made, looking at date-time ranges. My query works, but it takes ~ 10 minutes to complete only 95,000 records, which is too large. Any ideas for optimization?

SELECT r.*, rr.ChannelsActive 'ChannelsActive' FROM #rg r OUTER APPLY ( SELECT SUM(1) AS ChannelsActive FROM #rg r_inner WHERE ( r_inner.CallStart BETWEEN r.CallStart AND r.CallEnd OR r_inner.CallEnd BETWEEN r.CallStart AND r.CallEnd OR r.CallStart BETWEEN r_inner.CallStart AND r_inner.CallEnd OR r.CallEnd BETWEEN r_inner.CallStart AND r_inner.CallEnd ) ) rr 

Data examples

 CREATE TABLE #rg ( CallStart DATETIME, CallEnd DATETIME ) CREATE INDEX ix1 ON #rg(CallStart, CallEnd) CREATE INDEX ix2 ON #rg(CallEnd, CallStart); WITH T(N, R) AS (SELECT TOP (95000) ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS RN, ABS(120 + 30 * SQRT(-2 * LOG(ABS(CAST(CAST(CRYPT_GEN_RANDOM(8) AS BIGINT) AS FLOAT) / 9223372036854775807))) * COS(2 * PI() * ABS(CAST(CAST(CRYPT_GEN_RANDOM(8) AS BIGINT) AS FLOAT) / 9223372036854775807))) FROM sys.all_objects o1, sys.all_objects o2) INSERT INTO #rg SELECT DATEADD(SECOND, N, GETDATE()), DATEADD(SECOND, N + R, GETDATE()) FROM T 

Answer 1

This should do it:

  ;WITH cteCallEvents As ( SELECT *, CallStart As EventTime, 1 As EventType FROM #rg r UNION ALL SELECT *, CallEnd As EventTime, 0 As EventType FROM #rg r ) , cteCallCounts As ( SELECT *, ROW_NUMBER() OVER(Order By EventTime) as EventCount, ROW_NUMBER() OVER(Partition By EventType Order By EventTime) as TypeCount FROM cteCallEvents ) SELECT *, 2*TypeCount - EventCount As OpenCalls FROM cteCallCounts WHERE EventType = 1 

It only takes a couple of seconds. It should work on any SQL Server 2005+.

Answer 2

Use SQL like this to get a list of start / end events ...

 Select CallStart, 1 As CallCount From #rg Union All Select CallEnd, -1 From #rg Order By CallStart 

... then consider this as a simple summarization task that can be solved differently depending on your version of SQL Server or can be easily fixed in the code if this is an option.