How to optimize T-SQL query which depends on execution time?

Question

I have a big (several millions rows) table with system events. I must to get recent event counts and I'm not shore how to do it in right way.

I created this view:

CREATE VIEW [dbo].[EventCounts] 
AS
  SELECT  
      (SELECT COUNT(1)
       FROM dbo.SystemEvents r
       WHERE r.Timestamp > DATEADD(MINUTE, -15, GETDATE())
         AND r.Timestamp <= GETDATE()) AS last15minEventCount,
      (SELECT COUNT(1)
       FROM dbo.SystemEvents r
       WHERE r.Timestamp > DATEADD(MINUTE, -30, GETDATE())
         AND r.Timestamp <= DATEADD(MINUTE, -15, GETDATE())) AS from15to30EventCount,
      (SELECT COUNT(1)
       FROM dbo.SystemEvents r
       WHERE r.Timestamp > DATEADD(MINUTE, -60, GETDATE())
         AND r.Timestamp <= DATEADD(MINUTE, -30, GETDATE())) AS from30to60EventCount,
     (SELECT COUNT(1)
      FROM dbo.SystemEvents r
      WHERE r.Timestamp <= DATEADD(MINUTE, -60, GETDATE())) AS olderThan60minEventCount

This view returns:

1. < 15 minutes events count;
2. 15 - 30 minutes events count;
3. 30 - 60 minutes events count;
4. older than 60 minutes events count.

Now my code runs 4 times over all big table and I want to optimize it. I can't use preaggregations, because this counts depends on execution time. I can't use indexed view because of same reason.

I guess I can add nonclustered index on Timestamp column, but code still need to read whole table get this counts, right?

Could you suggest how to optimize my query?

P.S. last15minEventCount, from15to30EventCount, from30to60EventCount - very small part of all events.

Answer 1

One suggestion, you can change the approach and write your query like following.

CREATE VIEW [dbo].[EventCounts] 
AS 
  WITH Boundary AS (
           SELECT Dateadd(MINUTE, -15, Getdate()) AS LOW, 
                  Getdate()                       AS HIGH, 
                  'last15minEventCount'           AS Label 
           UNION 
           SELECT Dateadd(MINUTE, -30, Getdate()) AS LOW, 
                  Dateadd(MINUTE, -15, Getdate()) AS HIGH, 
                  'from15to30EventCount'          as Label 
          --Additional Conditions 
          ) 
  SELECT Count(*), 
         B.Label 
  FROM   SystemEvents R 
         INNER JOIN Boundary B 
                 ON R.TimeStamp BETWEEN LOW AND HIGH 
  GROUP  BY B.Label 

By this you will get the output into rows, you you need to transform as columns, which should be straight forward I feel.

This should be fast as the query is now SARGable, you need to put non clustered index on TimpeStamp column.