SQL - 更新大表(900万条记录)的查询性能
问题描述:
我有一个拥有9亿条记录的数据库表。我处于需要更新该表中4个不同键的情况,方法是将它们连接到一个维并将事实表的键设置为该维的键。我已经编写了4个不同的SQL脚本(请参阅下面的示例)来执行更新,但问题是执行时间太长。该查询已运行超过20个小时,我甚至不知道它走多远,这需要多长时间。我有什么办法可以改善这一点,所以只需要几个小时就可以完成。添加索引会改善吗?SQL - 更新大表(900万条记录)的查询性能
UPDATE f
SET f.ClientKey = c.ClientKey
FROM dbo.FactSales f
JOIN dbo.DimClient c
ON f.ClientId = c.ClientId
答
- 脚本外键。放下它们。
- 更新列上的脚本索引(不属于条件的一部分)。放下它们。
- 禁用触发器(如果存在)。
- 禁用所有可以锁定的进程(= all,include选择)。
- 更新您的钥匙。
- 重新创建您的外键,索引,启用触发器。
- 很高兴。
5的注释 - 从目标表中只准备所有新源代码的主键,并做一个语句。这意味着联接的成本更低,并且只有一个联接。
答
可以使用这个不填满事务日志
select 1
while(@@rowcount > 0)
begin
UPDATE f
SET top (100000) f.ClientKey = c.ClientKey
FROM dbo.FactSales f
JOIN dbo.DimClient c
ON f.ClientId = c.ClientId
AND f.ClientKey != c.ClientKey
end
如果您需要更新4个不同的密钥,然后做一次全部
大部分的成本是获取锁
禁用˚F .ClientKey,运行更新,然后重建它
如果您确定DimClient不会更改with (nolock)
但需要确保
如果您是唯一需要更新FactSales的过程,请使用tablock holdlock
答
使用正确的值创建新表。之后添加索引,约束。如果可能,删除现有表并将新表重新命名为现有表。
在针对900,000,000行表执行此操作之前,您没有对较小的子集进行测试吗?哎哟。最坏的情况是更新列上的索引。我宁愿创建一个新表,编写一个包含所有4个连接的Select,插入/选择,删除和重命名。 – dnoeth
如果(或更可能'何时')这是要回滚... ouch ... –