结合临时表关联三表的Update查询性能优化方案求助

性能问题根因 #

原实现存在3个核心性能损耗点：

• 重复2次全量关联Table1和Table2，产生冗余的表扫描开销
• 创建2个临时表写入读出产生额外IO开销，且临时表无索引时关联会触发全表扫描
• 多次数据拷贝、临时表关联拉长了整体执行链路

优化方案 #

方案1：单语句直接更新（优先选择，无临时表开销）

省去所有临时表操作，一次性关联完成计算和更新，适合绝大多数场景：

UPDATE t1_737
SET 
  PYTRUOGB2 = ISNULL(t1_728.TRUOB, 0) + (ISNULL(t1_728.TRUGM, 0) / 6) + ISNULL(t1_728.NGLBBLR, 0),
  PYTRUOGM2 = (ISNULL(t1_728.TRUOB, 0) * 6) + ISNULL(t1_728.TRUGM, 0) + (ISNULL(t1_728.NGLBBLR, 0) * 6)
FROM Table1 t1_737
JOIN Table2 t2_737 
  ON t1_737.structureNodeId = t2_737.id
JOIN Table1 t1_728
  ON t1_728.PeriodId = 728
JOIN Table2 t2_728
  ON t1_728.structureNodeId = t2_728.id
  AND t2_728.Dellid = t2_737.Dellid
WHERE t1_737.PeriodId = 737

方案2：临时表优化（适合超大数据量场景）

如果Table1数据量超过千万级，单语句关联压力大，可以优化临时表使用逻辑，仅保留必要的临时表并加索引：

-- 仅创建1个临时表存储728周期的计算基础数据
SELECT
  t2.Dellid,
  t1.TRUOB,
  t1.TRUGM,
  t1.NGLBBLR
INTO #Temp728Data
FROM Table1 t1
JOIN Table2 t2
  ON t1.structureNodeId = t2.id
WHERE t1.PeriodId = 728

-- 给关联键建聚集索引加速后续关联
CREATE CLUSTERED INDEX IX_Temp728Data_Dellid ON #Temp728Data(Dellid)

-- 直接关联更新主表，省去第二个临时表的读写开销
UPDATE t1_737
SET 
  PYTRUOGB2 = ISNULL(t.TRUOB, 0) + (ISNULL(t.TRUGM, 0) / 6) + ISNULL(t.NGLBBLR, 0),
  PYTRUOGM2 = (ISNULL(t.TRUOB, 0) * 6) + ISNULL(t.TRUGM, 0) + (ISNULL(t.NGLBBLR, 0) * 6)
FROM Table1 t1_737
JOIN Table2 t2_737
  ON t1_737.structureNodeId = t2_737.id
JOIN #Temp728Data t
  ON t.Dellid = t2_737.Dellid
WHERE t1_737.PeriodId = 737

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

索引优化建议 #

配合上述方案添加覆盖索引，可进一步降低扫描开销：

• Table1添加联合索引：(PeriodId, structureNodeId) INCLUDE (TRUOB, TRUGM, NGLBBLR, PYTRUOGB2, PYTRUOGM2)，覆盖查询和更新需要的所有字段，避免回表查询
• Table2添加联合索引：(id) INCLUDE (Dellid)，关联时直接获取Dellid字段，无需回表

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