IF NOT EXISTS子查询嵌入大查询时出现严重性能瓶颈求助

我在将IF NOT EXISTS子查询嵌入到更大的查询中时，遇到了显著的性能差异。该子查询单独执行仅需约2秒，速度很快，但嵌入后整体执行时间骤增至4-5分钟。我已为相关表（包括时态表nd.tblReqMatSum）创建了合适的索引，tmp.##mytable是无索引的全局临时表，但它的数据量几乎始终很小，然而性能问题仍存在。

问题查询代码 #

declare @result int=0

IF NOT EXISTS (
    SELECT 1
    FROM tmp.##mytable t
    INNER JOIN nd.tblReqMatSum rms ON t.matnr = rms.matnr
    WHERE rms.isActive = 1
)    
BEGIN
    SET @result = 1; 
END

执行计划分析（基于提供的计划信息） #

从执行计划来看，核心问题集中在：

• 优化器选择了低效的连接策略，可能对大时态表nd.tblReqMatSum执行了全表扫描或范围过大的索引查找
• 无索引的全局临时表tmp.##mytable在关联时，引发了重复查找或高开销的哈希匹配操作
• rms.isActive = 1的过滤条件未被有效利用，导致扫描数据量远超必要范围

优化建议 #

• 给临时表添加索引：为tmp.##mytable的matnr字段创建非聚集索引，减少关联时的查找开销：

  CREATE NONCLUSTERED INDEX IX_##mytable_matnr ON tmp.##mytable(matnr)
  

• 强制使用循环连接：指定以小临时表为驱动表的循环连接，避免对大表的低效扫描：

  declare @result int=0
  
  IF NOT EXISTS (
      SELECT 1
      FROM tmp.##mytable t
      INNER LOOP JOIN nd.tblReqMatSum rms ON t.matnr = rms.matnr
      WHERE rms.isActive = 1
  )    
  BEGIN
      SET @result = 1; 
  END
  

• 优化时态表索引：创建包含matnr和isActive的覆盖索引，让过滤和关联操作直接通过索引完成：

  CREATE NONCLUSTERED INDEX IX_tblReqMatSum_matnr_isActive ON nd.tblReqMatSum(matnr, isActive)
  -- 如果查询需要其他字段，可添加到INCLUDE子句中
  -- INCLUDE(col1, col2)
  

• 改用表变量存储临时数据：将临时表数据存入带主键的表变量，帮助优化器更准确判断数据量，生成更优计划：

  declare @result int=0
  DECLARE @matnrs TABLE(matnr /* 替换为实际字段类型 */ PRIMARY KEY)
  INSERT INTO @matnrs SELECT matnr FROM tmp.##mytable
  
  IF NOT EXISTS (
      SELECT 1
      FROM @matnrs t
      INNER JOIN nd.tblReqMatSum rms ON t.matnr = rms.matnr
      WHERE rms.isActive = 1
  )    
  BEGIN
      SET @result = 1; 
  END
  

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