SQL Server-当WHERE原因是唯一ID时，执行多个更新的最有效方法

Question

假设我在SQL Server数据中有10-20k条记录需要更新，我需要执行以下操作：-

UPDATE dbo.MyTable SET Field1 = 95 WHERE MyTableID = 348923
UPDATE dbo.MyTable SET Field1 = 90 WHERE MyTableID = 348924
UPDATE dbo.MyTable SET Field1 = 100 WHERE MyTableID = 348925

MyTableID是主键，因此保证每次都不同。我想知道，当你开始背靠背地获得10k到20k甚至更多的UPDATE语句时，最有效的运行方式是什么，因为我读到每个UPDATE都是对该表的查找，该表已经很大了。

我已经考虑并意识到：-

• 光标(目前看起来更可取(
• 像上面这样的单一更新列表，只处理缓慢

有人能建议什么是最有效的方法吗？

谢谢。

Answer 1

运行多个查询可能是一种有效的方法，因为它可以利用主键索引。

你也可以尝试使用条件表达式，比如：

UPDATE dbo.MyTable 
SET Field1 = case MyTableID
WHEN 348923 HEN 95
WHEN 348924 THEN 90
WHEN 348924 THEN 100
END
WHERE MyTableID IN (348923, 348924, 348925)

如果要更新的id列表确实在范围内，则WHERE子句可以以以下形式进行优化，这将利用索引：

WHERE MyTableID BETWEEN 348923 AND 348925

Answer 2

在SQL Server中，我建议使用JOIN:

UPDATE t
SET Field1 = v.Field1
FROM dbo.MyTable t JOIN
(VALUES (95, 348923),
(90, 348924),
(100, 348925)
) v(Field1, MyTableId)
ON v.MyTableId = t.MyTableId;

这应该具有与使用CASE基本相同的性能。我发现它更容易管理。如果要设置多个列，则要简单得多。

Answer 3

通过创建如下所示的表变量来准备一个脚本模板来处理更新

declare @your_tbl_name table
{
MyTableID int,
Field1 int
}

将您的所有值插入此表变量

insert into @your_tbl_name
select Field1,MyTableID from your [actualsource]

内部将该表变量与实际表连接，并更新值。

update t 
set t.Field1 = tbl.Field1
from dbo.MyTable t 
inner join @your_tbl_name tbl
on t.MyTableID = tbl.MyTableID

Answer 4

我认为Gordon Linoff提供了一个很好的方法。为了确定这是否可能，你需要即时更新还是可以批量更新？

如果可以对它们进行批处理，请考虑更新需要反映在主表上的频率。您可能会更幸运地将所有更改添加到临时表中，然后在以后运行更新。这实际上取决于你需要的表现，以及今天是什么影响了你的表现。如果有一群单独的用户同时运行更新，并且每个用户都需要能够立即更新单独的数据，那么需要考虑不同的方法。但是，如果您可以处理每隔一段时间更新这些值，请对其进行批处理！我收到了向我的公司销售产品的公司的大量更新，这是我为提高效率而不得不采取的路线。

以下是如果您可以使用暂存表，该批处理的外观。

--Creates a staging table
create table dbo.StageUpdates
(
ID int primary key
, NewValue int
)
--Adds values to update to staging table
insert into dbo.StageUpdates
VALUES
(348923, 95)
,(348924, 90)
,(348925, 100)
--Update values
update dbo.MyTable
set Field1 = NewValue
from dbo.MyTable as mt
inner join dbo.StageUpdates as su on mt.ID = su.ID
where su.NewValue <> mt.Field1 --Modify this if either value can be null.
--Clear updated values from stage
delete from dbo.StageUpdates
from dbo.StageUpdates as su
inner join dbo.MyTable as mt on su.ID = mt.ID
where su.NewValue <> mt.Field1 --Modify this if either value can be null.