双表UPSERT操作效率优化咨询：预填充初始值是否更优？

我需要对TableA与TableB两张表执行UPSERT操作，初始实现代码如下：

UPDATE TableA 
SET a = 'a', b = 'b', date = GETDATE() 
WHERE c = 'c'

IF @@ROWCOUNT = 0 
    INSERT INTO TableA (c, a, b, date) 
    VALUES ('c', 'a', 'b', GETDATE())

UPDATE TableB 
SET d = 'd', date = GETDATE() 
WHERE e = 'e' AND f = 'f' 

IF @@ROWCOUNT = 0 
    INSERT INTO TableB (e, f, d, date) 
    VALUES ('e', 'f', 'd', GETDATE())

每张表约含1万行数据，该操作每秒需执行100次，需尽可能提升执行速度。我尝试用事务封装操作，代码如下：

BEGIN TRANSACTION;

UPDATE TableA
SET a = 'a', b = 'b', date = GETDATE()
WHERE c = 'c';

IF @@ROWCOUNT = 0
BEGIN
    INSERT INTO TableA (c, a, b, date)
    VALUES ('c', 'a', 'b', GETDATE());
END

UPDATE TableB
SET d = 'd', date = GETDATE()
WHERE e = 'e' AND f = 'f';

IF @@ROWCOUNT = 0
BEGIN
    INSERT INTO TableB (e, f, d, date)
    VALUES ('e', 'f', 'd', GETDATE());
END

COMMIT TRANSACTION;

由于插入逻辑仅针对每个设备执行一次，其余场景均为更新操作，请问当前事务方案是否高效？是否应预填充表的初始值以省略插入逻辑？

1. 当前事务方案的效率分析 #

你的事务封装方案相比初始无事务版本，在数据一致性上更可靠，但性能层面还有优化空间：

• 事务的优势：将两个表的UPSERT操作纳入原子事务，避免了中间出现部分成功（比如TableA更新成功但TableB插入失败）的情况，保证了数据完整性。对于每秒100次的操作频率，单事务内的两行级操作（假设c是TableA的唯一键，e+f是TableB的复合唯一键）锁持有时间很短，不会造成明显的锁竞争。
• 性能瓶颈：当前写法是"先尝试更新，再判断插入"，会对目标表发起两次逻辑访问（一次UPDATE扫描，一次可能的INSERT）。对于绝大多数场景是更新的情况，虽然INSERT分支很少触发，但每次UPDATE后的@@ROWCOUNT判断仍会带来微小的开销。

更高效的UPSERT写法优化 #

可以改用SQL Server的MERGE语句（兼容SQL Server 2008+）或者INSERT...ON CONFLICT UPDATE（仅SQL Server 2022+支持），将两次表访问合并为一次，减少IO开销：

MERGE写法示例（TableA）：

MERGE TableA WITH (HOLDLOCK) AS target
USING (SELECT 'c' AS c, 'a' AS a, 'b' AS b) AS source
ON target.c = source.c
WHEN MATCHED THEN
    UPDATE SET a = source.a, b = source.b, date = GETDATE()
WHEN NOT MATCHED THEN
    INSERT (c, a, b, date) VALUES (source.c, source.a, source.b, GETDATE());

WITH (HOLDLOCK)可以避免并发场景下的竞争条件（比如两个会话同时尝试插入同一行），保证UPSERT的原子性。

2. 是否应预填充初始值省略插入逻辑 #

如果你的设备总数是固定且可提前获知的，预填充初始值是非常值得的优化方案，理由如下：

• 完全消除分支逻辑：每次操作直接执行UPDATE即可，无需判断@@ROWCOUNT，减少执行路径的复杂度，提升执行效率。对于每秒100次的高频操作，累计下来的性能提升会很明显。
• 简化代码：无需维护UPSERT的分支逻辑，代码更简洁，也减少了出错的可能。

但如果设备会动态新增，预填充就不可行——你无法提前预知所有设备ID，此时仍需保留UPSERT逻辑处理新增场景。

另外，预填充的存储成本可以忽略：1万行数据的表，即使预填充所有设备的初始值，占用的存储空间也极小，不会成为问题。

总结 #

• 事务方案本身是可靠的，但可以通过MERGE或SQL Server 2022+的UPSERT语法进一步优化性能，减少表访问次数。
• 若设备列表固定，预填充初始值是最优选择，能最大化执行速度；若设备会动态新增，优化UPSERT写法更实际。

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