假设有一个包含两列的简单审计表(在生产中有更多列)：
ID | Date
处理请求时,我们在此表中添加一条记录.
请求分批处理,批处理中可以有任意数量的项目.对于每个项目,我们将添加一条记录.批次之间将存在至少2秒的延迟(该数量是可配置的).
性能是通过每单位时间(例如每秒)处理请求的速度来衡量的.考虑这个示例数据(2个集群,项目数量相同,仅用于演示目的)：
--2016-01-29 10:27:25.603 --2016-01-29 10:27:25.620 --2016-01-29 10:27:25.637 --2016-01-29 10:27:25.653 --2016-01-29 10:27:25.723 --Avg time between requests = 24ms --2016-01-29 10:27:34.647 --2016-01-29 10:27:34.667 --2016-01-29 10:27:34.680 --2016-01-29 10:27:34.690 --2016-01-29 10:27:34.707 --Avg time = 12ms
我们可以说,在最坏的情况下,每秒可以处理41.67个请求,最多可以处理83.33个请求.很高兴知道平均批次性能.
编辑：要使结果具有统计显着性,丢弃批次可能比小于10个项目(可配置)更有用.
也许我已经过度简化了您的请求,但请考虑以下内容
Declare @YourTable table (ID int,Date datetime)
Insert Into @YourTable values
( 1,'2016-01-29 10:27:25.603'),( 2,'2016-01-29 10:27:25.620'),( 3,'2016-01-29 10:27:25.637'),( 4,'2016-01-29 10:27:25.653'),( 5,'2016-01-29 10:27:25.723'),( 6,'2016-01-29 10:27:34.647'),( 7,'2016-01-29 10:27:34.667'),( 8,'2016-01-29 10:27:34.680'),( 9,'2016-01-29 10:27:34.690'),(10,'2016-01-29 10:27:34.707')
Declare @BatchSecondsGap int = 2 -- Seconds Between Batches
Declare @MinObservations int = 5 -- Batch must n or greater
;with cte as (
Select *,Cnt = sum(1) over (Partition By Batch)
From (
Select *,Batch = sum(Flg) over (Order By Date)
From (
Select ID,Date,Flg = case when DateDiff(SECOND,Lag(Date,1,null) over (Order By Date),Date)>=@BatchSecondsGap then 1 else 0 end,MS = case when DateDiff(SECOND,Date) over (Order By Date),Date)>=@BatchSecondsGap then 0 else DateDiff(MILLISECOND,Date) end
From @YourTable
) A
) B
)
Select Title = 'Total',DateR1 = min(Date),DateR2 = max(Date),BatchCnt = count(Distinct Batch),TransCnt = count(*),MS_Ttl = sum(MS),MS_Avg = avg(MS*1.0),MS_Std = stdev(MS)
From cte
Where Cnt>=@MinObservations
Union All
Select Title = concat('Batch ',Batch),MS_Std = stdev(MS)
From cte
Where Cnt>=@MinObservations
Group By Batch
返回
下图说明您不会因批次之间的时间而受到惩罚,因此它将成为最终结果的简单聚合
