why performance of splicing update sql is better than using batch executor?

19 views
Skip to first unread message

cjl

unread,
May 22, 2022, 12:13:02 PMMay 22
to mybatis-user
The row of table is 20 millions, the row of waiting to update is 10k.Splicing update sql like that UPDATE xx SET a = x WHERE id = x;UPDATE xx SET a = x WHERE id = x;UPDATE xx SET a = x WHERE id = x;(in one transactional) takes 700ms, but using batch executor of mybatis takes 5300ms.

Its inconceivable, because the performance of splicing insert sql like that INSERT INTO XX(XX,XX) VALUES (XX,XX),(XX,XX),(XX,XX) is lower than using batch executor.

PS i have set rewriteBatchedStatements=true and allowMultiQueries=true.

cjl

unread,
May 23, 2022, 10:06:49 PMMay 23
to mybatis-user
This is example of batch executor.
```java
    public void batchUpdate(List<People> peopleList) {
        SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false);
        PeopleMapper peopleMapper = sqlSession.getMapper(PeopleMapper.class);
        try {
            for (People people : peopleList) {
                peopleMapper.update(people);  
            }
            sqlSession.commit();
        } catch (Exception e) {
            sqlSession.rollback();
        } finally {
            sqlSession.close();
        }
    }


    <update id="update" parameterType="com.lb.springboot.DO.People">
        update people set
          name = #{name},
          age = #{age},
          gmt_create = #{gmtCreate},
          gmt_modify = #{gmtModify},
          `type` = #{type},
          location = #{location},
          sex = #{sex},
          class = #{clazz},
          school = #{school},
          rand = #{rand}
        where id = #{id}
    </update>
```

This is example of Splicing update SQL.
```xml
    <update id="batchUpdate" parameterType="java.util.List">
        <foreach collection="peopleList" item="item" index="index" open="" close="" separator=";">
            update people
            <set>
                name = #{item.name},
                age = #{item.age},
                gmt_create = #{item.gmtCreate},
                gmt_modify = #{item.gmtModify},
                `type` = #{item.type},
                location = #{item.location},
                sex = #{item.sex},
                class = #{item.clazz},
                school = #{item.school},
                rand = #{item.rand}
            </set>
            where id = #{item.id}
        </foreach>
    </update>
```

The length of peopleList is 10k.
Reply all
Reply to author
Forward
0 new messages