优化sql更新

Question

我们有 2 个表，分别称为 TableToUpdate 和 Dates。 我们需要通过查看其他表日期来更新TableToUpdate的EndTimeKey列。 我们运行下面的 sql 来执行此操作，但需要很长时间才能完成。

表 TableToUpdate 有 6M 条记录。 表日期有 5000 条记录。

我们如何优化它？

感谢您的回复！

update TableToUpdate set
EndTimeKey = DATE_NO
from Dates where EndTime = DATE

Answer 1

您可能会更新 600 万条记录，无论如何这都不会很快。 但是，请查看您的执行计划并查看它是否使用索引。

还可以批量运行，这在更新大量记录时通常会更快。 在数据库负载很少的非工作时间进行更新，这将减少潜在的锁定问题。 确保两个表之间的数据类型相同，这样您就不必进行任何隐式转换。

看看你正在更新的表，上面有触发器吗？ 根据触发器的编写方式，这可能会严重减慢许多记录的更新速度（特别是如果不太聪明的人决定在触发器中放置游标或循环而不是编写基于集合的代码）。

另外，这里还有一些我要添加的内容（我还更改了它显示显式显示连接）

update t
set EndTimeKey = DATE_NO
from TableToUpdate t
Join Dates D on t.EndTime = d.DATE
where EndTimeKey <> DATE_NO

更新已经匹配的记录没有意义。

Answer 2

对于如此大量的数据，您可能最好创建一个 SELECT 查询，该查询会生成一个结果集，其中包含更新的值，正如您希望看到的新表一样。 接下来，通过创建表并使用 INSERT INTO 或通过更改 SELECT 添加 INTO 来创建新表，将它们选择到新表中（可能是“NewTableToUpdate”）。

接下来使用 sp_rename 将“TableToUpdate”重命名为“OLDTableToUpdate”，将“NEWTableToUpdate”重命名为“TableToUpdate”，然后按照在原始表上创建索引的方式创建索引。

根据我的经验，我发现这是实现此类重大变化的最快方法。 HTH。

额外的想法...如果您的表上有聚集索引，则将 ORDER BY 添加到您的 SELECT 语句中，以确保它以与聚集索引相同的顺序插入到新表中。 这将显着加快索引创建速度。

Answer 3

您可以在相关字段上设置一些索引（按相关顺序），即：endtimekey 和 endtime。 不要对此抱太大期望。 您可以检查的另一件事是是否有其他约束来限制查询结果。

您还可以创建一个视图，为每个 tabletoupdate.endtimekey 返回正确的 date_no。

如果您的 dbms 确实支持此类内容，也许您可​​以编写一个存储过程 - 因为这确实会加速更新。

Answer 4

我在这里注意到几件事，EndTimeKey 真的是密钥吗？ 如果是这样，它可能有一个索引，如果是这样，速度（或缺乏）将更新索引，同时也进行数据的实际更新，解决方案删除索引，运行更新重新应用索引。

另一个问题可能是 Sql 的事务性质 - 当您进行此更新时，它会记录每个更改，以便在发生故障时可以回滚。 此更新看起来非常简单，因此您可以批量应用它，即

update TableToUpdate setEndTimeKey = DATE_NOfrom Dates where EndTime = DATE
where TableToUpdateId between 1 and 100000

这会将您的更新分成可管理的大小块 - 至少您会知道每个块将花费多长时间。

另一种选择是在 EndTime 列上放置索引，可能必须执行全表扫描。

真正的答案是查看正在生成的查询计划。 正如您所看到的，查询运行缓慢的原因有很多 - 这些只是一些需要快速检查的原因。