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,

假设我有主键 ID，例如，

suppose I have primary key ids like,

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.

单个 update 的缺点是总体上可能更快，但它可能会锁定底层资源更长的时间.例如，每次更新可能需要 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屋！