Server更新索引设计

Question

我有一张桌子，IDMAP和DML：

CREATE TABLE tempdb2.dbo.idmaptemp (
    OldId varchar(20),
    CV_ModStamp datetimeoffset,
    NewId varchar(20),
    RestoreComplete bit,
    RestoreErrorMessage varchar(1000),
    OperationType varchar(20)
)

在定义它时，它已经包含了一组预定义的行(约100万行)。当恢复操作完成后，我必须更新表上的NewId、RestoreComplete、RestoreErrorMessage。声明如下：

update tempdb2.dbo.IdMaptemp set NewId = 'xxx', RestoreComplete = 'false', RestoreErrorMessage = 'error' where OldId = 'ABC';

Java应用程序在内存中有大约100万个值，并且必须用上面的语句更新值。数据库被设置为自动提交关闭，并随批处理(批处理大小500)而变化。

我尝试了两种使用OldId字段进行索引的方法：

1. 聚集索引-执行计划列表作为聚集索引更新(100%成本)。这是因为叶子是要更新的行，这些行将触发索引更新。我就在这儿吗？
2. 非聚集索引-执行计划列表为更新(75%)和寻求(25%)。

在数据库表上进行大规模更新时，还有其他的速度可以实现吗？该表无法清除并重新插入，因为还有其他行不受更新的影响。每批500行样本的聚集索引大约需要7小时才能更新。

我应该选择非聚集索引选项吗？

Answer 1

更改大型表的聚集索引是一个昂贵的建议。表的聚集索引是为整个表定义的，而不是为行子集定义的。

如果您将oldid作为聚集索引，并且只想提高批处理性能，请考虑允许db参与批处理过程，而不是应用程序/java层。要求数据库一次更新数以百万计的行1行，这是一个昂贵的提议。用批处理填充临时表，然后让SQL一次更新整个批处理，这是提高性能的好方法。

insert #temptable (OldId,NewId)
...

Update
set T1.NewId = T2.NewId
T1
from
T1 join #tempTable T2
on T1.OldId = T2.OldId

如果您可以计算新id，请考虑另一个批处理策略。

update tempdb2.dbo.IdMaptemp top 1000 set NewId = 'xxx', RestoreComplete = 'false', 
    RestoreErrorMessage = 'error' where NewId is null;

如果您真的想要创建一个以NewId作为聚集索引的新表，则可以根据需要创建新表。

insert into NewTable()
select top 10000 *
from OldTable O
left join NewTable N
on O.OldId = N.OldId
where N.OldId is null

做完后，把旧桌子放下。

注意:您的id需要是20个字节吗？通常，聚集索引要么是int -4字节，要么是bigint-8字节。

如果这是一次性的事情，那么更改大型持久表上的聚集索引是值得的。如果旧的数据总是在获取newid值的过程中，而这正是您所拥有的工作流，那么我就不会麻烦地更改持久表的聚集索引了。只需将旧的作为聚集索引。NewId听起来像一个代孕密钥。