使用EXCEPT的SQL查询运行过慢 如何进行性能优化

瓶颈根因定位 #

• EXCEPT运算符会隐式对两个结果集执行全量排序+去重操作，10万行业务表和40万行视图结果的排序开销是核心耗时点
• 多层嵌套子查询+NOT IN的写法会导致业务表被反复扫描，且NOT IN在子查询返回NULL值时会触发逻辑错误，无符合条件的行被删除
• 视图过滤条件year(LastChgDateTime) = 9999为非SARGable表达式，无法命中LastChgDateTime字段的索引，每次执行都会扫描视图全量数据
• 视图内部包含4次JOIN+1次UNION的复杂逻辑，在子查询中被调用时会重复执行，无结果缓存复用
• 原SQL存在字段拼写错误：子查询中的ColumH应为ColumnH，会直接导致执行报错或逻辑错误，需优先修正

优化方案 #

1. 逻辑改写：替换EXCEPT+NOT IN组合 #

改用LEFT JOIN + IS NULL实现差集判断，避免排序开销，同时规避NOT IN的空值问题。

2. 过滤条件优化 #

将非SARGable的时间判断改写为范围查询，支持索引命中：
把year(LastChgDateTime) = 9999替换为LastChgDateTime >= '9999-01-01 00:00:00' AND LastChgDateTime < '10000-01-01 00:00:00'

3. 视图结果物化 #

提前将视图中过滤后的目标字段存入临时表并创建联合索引，避免重复执行视图内部的复杂关联逻辑。

4. 新增覆盖索引 #

• 业务表[SCHEMA].[TABLE]新增联合索引：(ColumnB, ColumnG, ColumnH, ColumnI)，覆盖过滤条件和关联字段，避免回表查询
• 临时表新增联合索引：(ColumnG, ColumnH, ColumnI)，加速关联匹配

5. 可选优化：分批删除避免长事务 #

如果删除数据量较大，可添加TOP(1000)分批执行删除并提交事务，避免长时间锁表影响业务正常读写。

改写后SQL示例 #

-- 1. 物化视图过滤后的数据，避免重复执行视图内部逻辑
SELECT ColumnG, ColumnH, ColumnI 
INTO #TempViewFiltered
FROM [DB].[SCHEMA].[VIEW]
WHERE ColumnB = 'Active'
  AND LastChgDateTime >= '9999-01-01'
  AND LastChgDateTime < '10000-01-01'

-- 给临时表加联合索引，加速关联匹配
CREATE NONCLUSTERED INDEX IX_TempView_GHI ON #TempViewFiltered (ColumnG, ColumnH, ColumnI)

-- 2. 执行删除操作，完全对齐原逻辑且性能提升明显
DELETE t
FROM [DB].[SCHEMA].[TABLE] t
WHERE t.ColumnB NOT IN (4,6)
  AND NOT EXISTS (
    SELECT 1
    FROM [DB].[SCHEMA].[TABLE] t_inner
    LEFT JOIN #TempViewFiltered v
      ON t_inner.ColumnG = v.ColumnG
      AND t_inner.ColumnH = v.ColumnH
      AND t_inner.ColumnI = v.ColumnI
    WHERE t_inner.ColumnG = t.ColumnG
      AND v.ColumnG IS NULL
  )

-- 清理临时表
DROP TABLE IF EXISTS #TempViewFiltered

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