在db2中的批量更新速度比单次更新速度快吗？ [英] Is bulk update faster than single update in db2?

问题描述

我有一个10列的表，在该表中有数千万的行。
在某些情况下，我想一次更新10K以上的记录。目前，我的场景代码按顺序工作，如下所示：

I have a Table with 10 columns and in that table I have thousands/millions of rows. In some scenario, I want to update more than 10K records at a time. currently my scenario code works sequentially like,

for i in (primary key ids for all records to be updated)
     executeupdate(i)

我以为是而不是运行相同的查询10K次，我会添加所有的ID在一个字符串中运行单个更新查询，如

what I thought is instead of running same query 10K times, I will add all ids in a string and run a single update query like,

executeupdate(all ids) 

实际的数据库查询可以这样，

actual DB queries can be like this,

假设我有主键标签，

10001,10002,10003,10004,10005

所以在第一种情况我的查询将像

so in first case My queries will be like

update tab1 set status="xyz" where Id="10001"
update tab1 set status="xyz" where Id="10002"
update tab1 set status="xyz" where Id="10003"
update tab1 set status="xyz" where Id="10004"
update tab1 set status="xyz" where Id="10005"

和我的批量更新查询将如下所示

and My bulk update query will be like,

update tab1 set status="xyz" where id in ("10001","10002","10003","10004","10005")

所以我的问题是，我会通过批量更新
或总查询执行时间来获得任何性能改进（执行时间）相同于每个记录索引扫描将发生和更新将发生？

so My question is, will I get any Performance improvement (executime time) by doing bulk update or total query execution time will be same as for each record index scan will happen and update will take place?

注意：我正在使用DB2 9.5作为数据库

谢谢。

推荐答案

通常，无论数据库如何，批量更新将更快。当然，您可以测试两者的性能，并回报。

In general, a "bulk" update will be faster, regardless of database. Of course, you can test the performance of the two, and report back.

每次调用 update 需要一个一堆开销，在处理查询方面，在表/页/行上设置锁。单独的更新整合了这个开销。

Each call to update requires a bunch of overhead, in terms of processing the query, setting up locks on tables/pages/rows. Doing a single update consolidates this overhead.

单个更新的缺点是它总体上可能会更快，但是它可能会锁定潜在资源较长的时间。例如，单个更新可能需要10毫秒，其中1,000个时间为10秒。但是，没有资源被锁定超过10毫秒。批量更新可能需要5秒钟，但资源将在此期间被锁定。

The downside to a single update is that it might be faster overall, but it might lock underlying resources for longer periods of time. For instance, the single updates might take 10 milliseconds each, for an elapsed time of 10 seconds for 1,000 of them. However, no resource is locked for more than 10 milliseconds. The bulk update might take 5 seconds, but the resources would be locked for more of this period.

为了加快这些更新速度，请确保 id 被索引。

To speed these updates, be sure that id is indexed.

我应该注意。这是一般原则。我没有专门测试DB2上的单一或多个更新性能。

I should note. This is a general principle. I have not specifically tested single versus multiple update performance on DB2.

这篇关于在db2中的批量更新速度比单次更新速度快吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！