Average Insertion Rate

Question

I've got a table with a column indicating the date and time each row was inserted into the table. I'm trying to get a statistics for the average and peak rates of insertions:

• Peak insertions per minute
• Peak insertions per second
• Average insertions per minute
• Average insertions per second

I can envisage a solution using a GROUP BY to put the data into "buckets" (one for each interval) and then average the count of items in each, however it seems a very clunky solution.

Is there a more elegant T-SQL solution to this problem?

Answer 1

Grouping Sets are the way to go, they're are intended for this very application of grouping by multiple sets of grouping attributes (grouping sets) in one query, and should result in better execution plans i.e. better performance:

-- if you weren't grouping by minutes and seconds this would
-- probably look more 'elegant'
SELECT      
    GROUPING_ID(
        YEAR(orderdate), 
        MONTH(orderdate), 
        DAY(orderdate), 
        DATEPART(hour, orderdate),
        DATEPART(MINUTE, orderdate),
        DATEPART(SECOND, orderdate)) AS grp_id,
    MAX([Insertions])                AS max_insertions,
    AVG([Average])                   AS avg_insertions,
    YEAR(orderdate)                  AS order_year,
    MONTH(orderdate)                 AS order_month, 
    DAY(orderdate)                   AS order_day, 
    DATEPART(HOUR, orderdate)        AS order_hour,
    DATEPART(MINUTE, orderdate)      AS order_minute,
    DATEPART(SECOND, orderdate)      AS order_second -- this will be null if the grouping set is minute
FROM Sales.Orders
GROUP BY
   GROUPING SETS
   (
       (
            -- grouping set 1: order second
            YEAR(orderdate), 
            MONTH(orderdate), 
            DAY(orderdate), 
            DATEPART(hour, orderdate),
            DATEPART(MINUTE, orderdate),
            DATEPART(SECOND, orderdate)
        ),
        (
            -- grouping set 2: order minute
            YEAR(orderdate), 
            MONTH(orderdate), 
            DAY(orderdate), 
            DATEPART(hour, orderdate),
            DATEPART(MINUTE, orderdate)
        )
    );

Answer 2

GROUP BY is the way to go.

I would just make a CTE for each time interval you want, and select the max for each one:

;WITH CTEMinute AS
(
    SELECT  YEAR(datefield) yr, 
            MONTH(datefield) mo, 
            DAY(datefield) d, 
            DATEPART(hour, datefield) hr, 
            DATEPART(minute, datefield) Mint, 
            COUNT(*) as 'Inserts'
    FROM MyTable
    GROUP BY    YEAR(datefield), 
                MONTH(datefield), 
                DAY(datefield), 
                DATEPART(hour, datefield), 
                DATEPART(minute, datefield)
)
,CTESecond AS
(
    SELECT YEAR(datefield) yr, 
            MONTH(datefield) mo, 
            DAY(datefield) d, 
            DATEPART(hour, datefield) hr, 
            DATEPART(minute, datefield) Mint, 
            DATEPART(second, datefield) sec, 
            COUNT(*) as 'Inserts'
    FROM MyTable
    GROUP BY    YEAR(datefield), 
                MONTH(datefield), 
                DAY(datefield), 
                DATEPART(hour, datefield), 
                DATEPART(minute, datefield), 
                DATEPART(second, datefield)

)

Then you can just select from those CTEs to get max/min/avg values per time unit.

If you want it to be more elegant you can potentially just make on CTE for as fine granularity as you are likely to want (i.e. milliseconds or whatever), and then you can SELECT/GROUP BY that.

The issue with doing that is CTEs don't really perform that well since they are basically disposable views with no indexes or anything, so aggregating a CTE within another query will quickly bog down.