Fastest way to update huge number of rows with input param List in MyBatis to Oracle db

Question

I'm updating huge amount of data by passing a variable List in MyBatis to Oracle DB.

Methods from this link are not efficient enough for me, the ways to commit update sql query line by line, for loop in sql query or Executor.batch service are way too slow from what I expect.

//one of the method i use
<update id="updateAll">
    BEGIN
        <foreach collection="list" item="item" index="index" separator=";">
            UPDATE <include refid="tableName"/>
            <set>
                item_price = ${item.price}, update_time = ${item.updateTime}
            </set>
            WHERE id = ${item.id}
        </foreach>
    ;END;
</update>

With the ways I tried, my system spend 10 - 30 seconds or maybe longer to complete the update. There will be around 10,000 rows of data per sec from server. Is there is any way to update at least 1-2k rows of data within 1 or 2 second in Oracle db?

score 2 · Accepted Answer · answered Jun 09 '19 at 13:40

Using batch executor is the recommended way, but you need to do it properly.
Two issues that I noticed.

Setting a proper batch size is important. The linked answer sends all the data at the end which is not efficient very much.
Using ${} to reference parameters makes each statement unique and prevents the driver from reusing the statement (the benefit of batch executor is lost, basically). See this FAQ for the difference between #{} and ${}.

Here is a typical batch operation using MyBatis.
As the best batchSize depends on various factors, you should measure the performance using the actual data.

int batchSize = 1000;
try (SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH)) {
  YourMapper mapper = sqlSession.getMapper(YourMapper.class);
  int size = list.size();
  for (int i = 0; i < size;) {
    mapper.update(list.get(i));
    i++;
    if (i % batchSize == 0 || i == size) {
      sqlSession.flushStatements();
      sqlSession.clearCache();
    }
  }
  sqlSession.commit();
}

And here is an efficient version of the update statement.

<update id="update">
  UPDATE <include refid="tableName" />
  SET
    item_price = #{item.price},
    update_time = #{item.updateTime}
  WHERE id = #{item.id}
</update>

arhhhh, I've spent a whole day on this just bcuz of a symbol.. For inserting it works fine!!! but somehow it hang and not throwing error at sql.flushstatement() while using update query.. Thanks anyway!! — JJ___, Jun 09 '19 at 15:58
A symbol can ruin our day sometimes :D Regarding the update issue, there may be row(s) that are locked by another thread. — ave, Jun 09 '19 at 16:35
hey in my case, sqlSession.flushStatements() [end of the loop] is committing the changes to database before the .commit() statement..? do you know why and how we can avoid this? FYI, I am using/declaring the session as openSession(ExecutorType.BATCH, false) — HookUp, Jun 22 '21 at 01:09

Fastest way to update huge number of rows with input param List in MyBatis to Oracle db

1 Answers1

Linked