我正在尝试通过查看日期时间范围来计算特定呼叫时的同时呼叫数.我的查询有效,但只需要约10分钟即可执行95,000条记录,这太长了.任何优化的想法？
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
示例数据
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
解决方法
这应该这样做：
;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
它最多需要几秒钟.应该适用于任何sql Server 2005.
