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

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.

It's 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.

I have set rewriteBatchedStatements=true and allowMultiQueries=true.

This is example of batch executor.

    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.

    <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.



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source