spring boot - Fastest way to update huge number of rows with input param List<T> 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?

Answer 1

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

1. Setting a proper batch size is important. The linked answer sends all the data at the end which is not efficient very much.
2. 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>