SQL使用CASE子句取列最值查询执行慢的优化方案咨询

以下优化点完全适配当前查询场景（基于[dbo].[tbSerialTracking]、[dbo].[tbPTransfer]两张表做设备维度统计、极值计算、时间范围统计、流转时长计算，用到表变量、窗口函数、LEFT JOIN语法）：

核心可落地优化项 #

• 替换表变量为带索引的临时表
  表变量不会生成准确的基数统计信息，SQL Server查询优化器默认会估算表变量仅返回1行数据，在和大表做关联时大概率会选择低效的嵌套循环关联算法，数据量稍大就会出现执行耗时陡增的问题。直接将DECLARE @tmp TABLE(...)定义的表变量替换为CREATE TABLE #tmp(...)创建的临时表，同时给临时表的关联字段、分组字段创建对应非聚簇索引，能让优化器生成准确度高很多的执行计划。
• 优化窗口函数计算逻辑，避免不必要的排序开销
  用到的LEAD/LAG以及极值统计窗口函数，性能开销90%以上来自PARTITION BY、ORDER BY字段触发的排序操作：
  • 如果只是统计不同IP对应设备记录的最早、最晚时间，直接用MIN(RecordTime) OVER (PARTITION BY DeviceID, IP)、MAX(RecordTime) OVER (PARTITION BY DeviceID, IP)即可，不需要用LAG/LEAD逐行偏移取值，减少逐行计算的开销
  • 确保窗口函数的分区、排序字段和基表的索引排序顺序一致，避免执行过程中出现额外的内存排序，数据量大时排序操作溢出到tempdb会导致耗时呈指数级上升
• 精简CASE聚合逻辑，减少中间结果集
  按设备维度取各列最小、最大值时，不要先在子查询里逐行做CASE判断生成大的中间结果集再在外层聚合，直接把CASE判断放到聚合函数内部，能大幅减少中间结果的扫描量：

  -- 低效写法：先生成中间表再聚合
  SELECT DeviceID, MAX(Col1_Val) Col1_Max, MIN(Col1_Val) Col1_Min
  FROM (
    SELECT DeviceID, CASE WHEN Col1 IS NOT NULL THEN Col1 END Col1_Val
    FROM [dbo].[tbSerialTracking]
  ) t GROUP BY DeviceID
  
  -- 高效写法：聚合内直接做CASE判断
  SELECT DeviceID,
    MAX(CASE WHEN Col1 IS NOT NULL THEN Col1 END) Col1_Max,
    MIN(CASE WHEN Col1 IS NOT NULL THEN Col1 END) Col1_Min
  FROM [dbo].[tbSerialTracking] GROUP BY DeviceID
  

• 优化LEFT JOIN关联逻辑，消除索引失效场景
  1. 关联前先做数据裁剪：不要直接对两张全量表做关联，先在子查询里过滤掉不需要的时间范围、无效状态的记录，把参与关联的结果集缩到最小再做JOIN
  2. 给基表创建匹配查询的覆盖索引：针对tbSerialTracking，建议创建包含DeviceID, IP, RecordTime以及所有需要统计极值的业务字段的非聚簇索引；针对tbPTransfer，建议创建包含关联键、产品标识、流转时间字段的非聚簇索引，确保关联和统计时不需要做回表查询
  3. 保证关联字段数据类型完全一致：不要在关联条件上对字段用CAST/CONVERT类型转换，否则会导致索引失效，触发全表扫描
• 简化产品流转时长计算逻辑
  如果是统计产品在单设备/全链路的总流转时长，直接按产品ID分组后用MAX(RecordTime) - MIN(RecordTime)计算即可，不需要用LEAD逐行取下一条记录时间再做差求和。如果确实需要统计相邻节点间的流转时长，配合前面提到的有序索引，窗口函数可以直接按索引顺序扫描取值，不需要额外排序。

快速排查耗时点的参考 #

如果调整后仍有性能问题，可以直接看实际执行计划里的高开销算子：

• 出现Table Scan/Index Scan（全表/全索引扫描）：说明对应表缺少匹配的过滤、关联索引
• 出现Key Lookup（键查找）：说明现有索引没有覆盖查询需要的字段，需要把缺失字段加到索引INCLUDE里
• 出现Sort/Hash Warning警告：说明排序/哈希关联的内存不足，数据溢出到了tempdb，需要通过前置数据裁剪、建有序索引减少参与计算的数据集大小

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