慢运行CTE查询的性能瓶颈排查求助

瓶颈分析与定位 #

我帮你梳理了几个核心的性能问题点，一个个拆解给你看：

• WHERE/JOIN条件里的函数调用导致索引失效
  你看这几个关键条件：

  • CONVERT(DATETIME, dbo.OC_VDAT_AUX.UDL40, 101) > DATEADD(hour, -24, GETDATE())
  • LEFT(dbo.OC_VDATA.PARTNO, 12) = i.SPEC_NO 以及关联OC_VDAT_AUX时的同款LEFT条件
  • RIGHT(dbo.OC_VDATA.PARTNO,6) IN (...)
    当你直接在列上套函数（CONVERT/LEFT/RIGHT）时，SQL Server没办法利用这些列上已有的索引，只能被迫做全表扫描——数据量越大，这个扫描的耗时就越夸张。尤其是UDL40用来过滤最近24小时的数据，要是没法快速定位到时间范围内的行，整个查询的基础效率就垮了。

• GROUP BY包含大量动态拼接字符串
  你的GROUP BY子句里堆了一堆CONCAT(RTRIM(...), ...)的计算表达式，SQL Server得先给每一行算出这些拼接值，再做分组聚合，这会消耗大量CPU和内存。而且这些拼接后的列几乎不可能有索引，分组时只能做大量的排序/哈希运算，进一步拖慢速度。

• 冗余的JOIN和重复条件
  你和ITEM_CODES表关联时，同时用了LEFT(OC_VDATA.PARTNO,12)=i.SPEC_NO和LEFT(OC_VDAT_AUX.PARTNOAUX,12)=i.SPEC_NO，但OC_VDATA和OC_VDAT_AUX已经通过PARTNO=PARTNOAUX关联了，这两个LEFT条件其实是重复的，多做了一次不必要的匹配检查。另外，你JOIN了PLANTS表，但查询全程没用到这个表的任何字段，这个JOIN完全是多余的，平白增加了数据关联的开销。

• 窗口函数的分区键用了拼接字符串
  ROW_NUMBER()的PARTITION BY用了CONCAT(RTRIM(UDL1), RTRIM(UDL6))这个计算值，窗口函数需要基于这个动态计算的结果来分区排序，又额外增加了计算负担。

针对性优化建议 #

给你几个具体的优化方向，能快速提升性能：

1. 避免在列上直接套函数，改用持久化计算列
   • 对于UDL40，如果这个字段存的是日期字符串，优先改成datetime类型；改不了的话，加一个持久化计算列：

     ALTER TABLE dbo.OC_VDAT_AUX ADD UDL40_DT AS CONVERT(DATETIME, UDL40, 101) PERSISTED;
     

     然后给UDL40_DT加索引，这样过滤时间的条件就能直接用UDL40_DT > DATEADD(hour, -24, GETDATE())，完美利用索引。
   • 对于PARTNO的前12位和后6位，同样加持久化计算列：

     ALTER TABLE dbo.OC_VDATA ADD PARTNO_PREFIX AS LEFT(PARTNO, 12) PERSISTED, PARTNO_SUFFIX AS RIGHT(PARTNO, 6) PERSISTED;
     ALTER TABLE dbo.OC_VDAT_AUX ADD PARTNOAUX_PREFIX AS LEFT(PARTNOAUX, 12) PERSISTED;
     

     之后JOIN和WHERE条件就用这些计算列，同时给它们加索引，彻底告别全表扫描。
2. 删掉冗余的JOIN
   直接移除和stagingPLM.dbo.PLANTS的JOIN，因为你根本没用到这个表的数据，纯纯浪费资源。
3. 简化GROUP BY和窗口函数的分区键
   GROUP BY里换成基础列（比如i.ITEM_CODE、OC_VDATA.UDL1、OC_VDATA.UDL6等），然后在SELECT里再做字符串拼接。窗口函数的PARTITION BY也换成RTRIM(OC_VDATA.UDL1)和RTRIM(OC_VDATA.UDL6)（如果列本身没有 trailing space，直接用原列就行），避免用动态拼接的字符串。
4. 优化现有索引
   给关联和过滤的核心字段建复合索引，比如给OC_VDATA建包含PARTNO、DATETIME、UDL1、UDL6的索引，给OC_VDAT_AUX建包含PARTNOAUX、DATETIMEAUX、UDL40、UDL28的索引，让JOIN和过滤更高效。

备注：内容来源于stack exchange，提问作者EricALionsFan