Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.3k views
in Technique[技术] by (71.8m points)

spring boot - Fastest way to update huge number of rows with input param List<T> in MyBatis to Oracle db

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?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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>

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...