如何优化耗时6分钟的T-SQL查询？

问题背景 #

原查询用于从自动化仓库的事件日志表LogEvents中，筛选出特定位置（presence = 7845）某一天的记录，并获取每个货箱（bac）对应的上一条和下一条事件的相关字段。但原查询使用了6个行相关子查询，导致执行耗时高达6分钟。

原查询代码：

SELECT  dateheure, bac, presence, reponse
    ,(select top 1 dateheure from LogEvents where bac = t1.bac and dateheure < t1.dateheure order by id desc) as dateheure_precedente
    ,(select top 1 presence from LogEvents where bac = t1.bac and dateheure < t1.dateheure order by id desc) as presence_precedente 
    ,(select top 1 reponse from LogEvents where bac = t1.bac and dateheure < t1.dateheure order by id desc) as reponse_precedente   
    ,(select top 1 dateheure from LogEvents where bac = t1.bac and dateheure > t1.dateheure order by id asc) as dateheure_suivante
    ,(select top 1 presence from LogEvents where bac = t1.bac and dateheure > t1.dateheure order by id asc) as presence_suivante
    ,(select top 1 reponse from LogEvents where bac = t1.bac and dateheure > t1.dateheure order by id asc) as reponse_suivante
FROM [alpla_log].[dbo].[LogEvents] t1 
WHERE 
    t1.presence = 7845
    AND dateheure BETWEEN '11/07/2024 00:00:00' AND '11/07/2024 23:59:59.997' 
ORDER BY id DESC

表结构：

CREATE TABLE LogEvents (
    id int IDENTITY NOT NULL PRIMARY KEY,
    dateheure datetime NULL,
    type varchar(50) NULL,
    msg varchar(MAX) NULL,
    presence int NULL,
    destination int NULL,
    status_prt int NULL,
    reponse int NULL,
    bac int NULL
);

性能瓶颈分析 #

原查询的核心问题是行相关子查询的重复执行：每返回一条符合WHERE条件的记录，就会触发6次对LogEvents表的扫描，去查找对应bac的前后记录。当匹配的记录数较多时，这种方式会导致表被扫描成千上万次，直接拉低性能。

优化方案 #

使用SQL Server内置的窗口函数LAG()和LEAD()，这两个函数专门用于在同一个分组内获取当前行的前一条（LAG）或后一条（LEAD）记录的数据，只需要扫描表一次即可完成所有计算，彻底避免重复扫描。

优化后的查询代码：

SELECT 
    dateheure, 
    bac, 
    presence, 
    reponse,
    -- 获取同bac分组内的上一条记录字段
    LAG(dateheure) OVER (PARTITION BY bac ORDER BY id) AS dateheure_precedente,
    LAG(presence) OVER (PARTITION BY bac ORDER BY id) AS presence_precedente,
    LAG(reponse) OVER (PARTITION BY bac ORDER BY id) AS reponse_precedente,
    -- 获取同bac分组内的下一条记录字段
    LEAD(dateheure) OVER (PARTITION BY bac ORDER BY id) AS dateheure_suivante,
    LEAD(presence) OVER (PARTITION BY bac ORDER BY id) AS presence_suivante,
    LEAD(reponse) OVER (PARTITION BY bac ORDER BY id) AS reponse_suivante
FROM [alpla_log].[dbo].[LogEvents]
WHERE 
    presence = 7845
    AND dateheure BETWEEN '11/07/2024 00:00:00' AND '11/07/2024 23:59:59.997' 
ORDER BY id DESC

索引优化补充

之前创建的索引CREATE NONCLUSTERED INDEX ON dbo.Logevents (bac, dateheure) INCLUDE (reponse)没有命中查询的WHERE条件（presence = 7845），建议创建覆盖索引，让查询可以直接从索引获取所有需要的数据，无需回表：

CREATE NONCLUSTERED INDEX IX_LogEvents_Presence_Bac_Dateheure 
ON dbo.LogEvents (presence, bac, dateheure) 
INCLUDE (id, reponse);

优化效果 #

使用LAG()和LEAD()函数后，查询执行时间直接降至约500ms，性能大幅提升。

内容的提问来源于stack exchange，提问作者CrocodileDundee