如何优化取决于执行时间的 T-SQL 查询

Question

我有一个包含系统事件的大(数百万行(表。我必须获得最近的事件计数，我不知道如何以正确的方式做到这一点。

我创建了这个视图：

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

此视图返回：

1. <15>
2. 15 - 30 分钟事件计数;
3. 30 - 60 分钟事件计数;
4. 超过 60 分钟的事件计数。

现在我的代码在所有大表上运行 4 次，我想优化它。我不能使用预聚合，因为这计数取决于执行时间。由于同样的原因，我无法使用索引视图。

我想我可以在时间戳列上添加非聚集索引，但代码仍然需要读取整个表来获取此计数，对吧？

你能建议如何优化我的查询吗？

P.S. last15minEventCount， from 15to 30EventCount， from 30to 60EventCount - 占所有事件的一小部分。

Answer 1

尝试这样的事情：

 SELECT COUNT(1),
        SUM(case when r.Timestamp > DATEADD(MINUTE, -15, GETDATE()) AND r.Timestamp <= GETDATE() 
                 then 1
                 else 0
            end) as last15minEventCount,
        sum(case when r.Timestamp > DATEADD(MINUTE, -30, GETDATE())AND r.Timestamp <= DATEADD(MINUTE, -15, GETDATE())
                 then 1
                 else 0 
                 end) as from15to30EventCount,
        sum(case when  r.Timestamp > DATEADD(MINUTE, -60, GETDATE()) AND r.Timestamp <= DATEADD(MINUTE, -30, GETDATE())
                 then 1
                 else 0 
                 end) as from30to60EventCount,
        sum(case when r.Timestamp <= DATEADD(MINUTE, -60, GETDATE()
                 then 1
                 else 0 
                 end) as olderThan60minEventCount,
 FROM dbo.SystemEvents r

Answer 2

一个建议，您可以更改方法并像下面这样编写查询。

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 

通过这种方式，您将输出转换为行，您需要转换为列，我觉得这应该是直截了当的。

这应该很快，因为查询现在可 SARGable，您需要将非聚集索引放在 TimpeStamp 列上。

Answer 3

要避免每个间隔进行一次扫描，您可以使用SUM(CASE ... END) .但是，为了使查询速度非常快，您应该维护一个包含总数的表，并在TimeStamp列上创建一个索引：

CREATE TABLE SystemEvents
(
    [TimeStamp] datetime
)
GO
CREATE INDEX IX_SystemEvents_TimeStamp
ON dbo.SystemEvents(TimeStamp)
CREATE TABLE SystemEventsTotalCount
(
    TotalCount int NOT NULL
)
GO
INSERT INTO SystemEventsTotalCount VALUES ((SELECT COUNT(*) FROM SystemEvents))
GO
CREATE TRIGGER SystemEvents_TotalCount
ON SystemEvents
FOR INSERT, DELETE
AS
    DECLARE @InsertedRows int = (SELECT COUNT(*) FROM inserted);
    DECLARE @DeletedRows int = (SELECT COUNT(*) FROM deleted);
    UPDATE SystemEventsTotalCount
    SET TotalCount = TotalCount + @InsertedRows - @DeletedRows
GO
GO
CREATE VIEW EventCounts
AS
WITH b AS
(
    SELECT 
        SUM(CASE WHEN r.Timestamp > DATEADD(MINUTE, -15, GETDATE()) AND r.Timestamp <= GETDATE() THEN 1 ELSE 0 END) AS Last15MinEventCount,
        SUM(CASE WHEN r.Timestamp > DATEADD(MINUTE, -30, GETDATE()) AND r.Timestamp <= DATEADD(MINUTE, -15, GETDATE()) THEN 1 ELSE 0 END) AS From15To30EventCount,
        SUM(CASE WHEN r.Timestamp > DATEADD(MINUTE, -60, GETDATE()) AND r.Timestamp <= DATEADD(MINUTE, -30, GETDATE()) THEN 1 ELSE 0 END) AS From30to60EventCount,
        (SELECT TotalCount FROM dbo.SystemEventsTotalCount) AS TotalEventCount
    FROM 
        dbo.SystemEvents r
    WHERE
        r.Timestamp > DATEADD(MINUTE, -60, GETDATE())
)
SELECT
    b.Last15MinEventCount, b.From15To30EventCount, b.From30to60EventCount,
    (b.TotalEventCount - b.Last15MinEventCount - b.From15To30EventCount - b.From30to60EventCount) AS OlderThan60EventCount
FROM b
GO
SELECT * FROM EventCounts