SQL: What is the best way to display (near) realtime site activity over a time interval? i.e. "Messages sent in the last hour"

The goal is to update the UI as frequently as possible but performance is a concern and hits to the database should be minimised.

I used Indexed Views to keep up to date stats on items that were not开发者_如何学Go for a specific time interval however this in not applicable as the date is non-deterministic.

Other options would be to:

• Run a job every X minutes to gather the stats.

• Add triggers to respond to insertions.

• Run a stored procedure every time / at a defined frequency. (Most of the tables concerned are already indexed on the date concerned)

Are there any other options I haven't identified and is there a 'preferred' solution to this kind of problem? How do the Job/Trigger/SP options compare?

If you are running over "fixed" intervals, you can use a lookup table that stores your clustered index key. Assuming the key is an INT Identity you could make a table like:

Time Datetime,
Qtr_Hour int,
Half_Hour int,
Hour int 

Run a query that executes:

SELECT COUNT(*)
FROM MyTable
WHERE ClusterKey > (SELECT Qtr_Hour
                    FROM LookupTable)

Then update the table with the new relevant value for MAX(ClusterKey).

You would need to work out details for your use case, but if you have a 1 row table that you just keep up to date as you query, and it uses the cluster key as a reference point, it should be very fast to get a delta on the number of rows since the last update.