是否应使用临时表优化多表删除查询？

一、临时表写法的效率优势 #

• 避免重复执行复杂查询：原写法中，获取ID的复杂查询会被执行两次（删除Table1和Table2时各跑一次）。如果这个查询涉及多表连接、聚合计算或者大量数据扫描，重复计算会白白消耗CPU和IO资源。用临时表的话，复杂查询只执行一次，结果存入临时表后，后续删除操作直接读取临时表数据，省去了重复计算的开销。
• 可给临时表加索引提速：如果临时表中的ID数量较多，给ID字段创建索引（比如CREATE CLUSTERED INDEX IX_Temp_ID ON #Temp(ID)），后续删除时匹配ID的速度会大幅提升，尤其是原表ID字段本身有索引的情况下，双重索引能让关联匹配效率更高。
• 执行计划更稳定：虽然现代数据库（如SQL Server）会尝试重用相同查询的执行计划，但生成复杂查询的执行计划本身存在一定开销。临时表写法只需要生成一次复杂查询的执行计划，后续删除操作的执行计划更简单、稳定，减少了优化器重新生成计划的消耗。

二、其他实用优化建议 #

• 用JOIN替代IN子句：在多数数据库中，DELETE结合JOIN的写法比IN子句效率更高，尤其是数据量较大时，优化器更容易生成高效的执行计划。示例代码：

  DELETE t1
  FROM Table1 t1
  JOIN #Temp tmp ON t1.ID = tmp.ID
  
  DELETE t2
  FROM Table2 t2
  JOIN #Temp tmp ON t2.ID = tmp.ID
  

• 批量删除大数量数据：如果要删除的ID数量非常多，一次性删除可能导致长时间锁表、事务日志暴涨。可以分批次删除，比如每次删1000条：

  WHILE EXISTS(SELECT 1 FROM #Temp)
  BEGIN
      -- 批量删Table1
      DELETE TOP(1000) t1
      FROM Table1 t1
      JOIN #Temp tmp ON t1.ID = tmp.ID
      
      -- 批量删Table2
      DELETE TOP(1000) t2
      FROM Table2 t2
      JOIN #Temp tmp ON t2.ID = tmp.ID
      
      -- 移除临时表中已处理的ID，避免重复操作
      DELETE TOP(1000) FROM #Temp
  END
  

• 确保原表ID字段有索引：如果Table1和Table2的ID字段没有索引，删除操作会触发全表扫描，效率极低。务必给这两个表的ID字段创建索引（主键默认是聚集索引，一般能满足需求）。
• 加事务保证原子性：如果需要确保Table1和Table2的删除操作要么全部成功、要么全部回滚，把逻辑包裹在事务里：

  BEGIN TRANSACTION
  BEGIN TRY
      SELECT (-- 你的复杂查询 --) INTO #Temp
      CREATE CLUSTERED INDEX IX_Temp_ID ON #Temp(ID)
      
      DELETE t1 FROM Table1 t1 JOIN #Temp tmp ON t1.ID = tmp.ID
      DELETE t2 FROM Table2 t2 JOIN #Temp tmp ON t2.ID = tmp.ID
      
      COMMIT TRANSACTION
  END TRY
  BEGIN CATCH
      ROLLBACK TRANSACTION
      -- 打印错误信息，方便排查
      PRINT ERROR_MESSAGE()
  END CATCH
  

• 选对临时对象类型：如果ID数量很少，用表变量（DECLARE @Temp TABLE(ID INT PRIMARY KEY)）代替临时表更轻便；但如果数据量大，临时表的统计信息更完善，优化器能生成更优的执行计划，这时候临时表更合适。
• 显式清理临时表（可选）：虽然会话结束后临时表会自动删除，但如果会话持续时间长，显式删除能及时释放资源：DROP TABLE IF EXISTS #Temp

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