使用mybatis(3.2.8版)插入2000条记录时出现性能问题

问题描述

我正在尝试在Employee表中批量插入2000条记录(使用mybatis).我的要求是:1.如果任何记录未能插入，则记录错误.2.即使其中任何一条记录失败，也要继续插入.3.如果任何一条记录失败，则不应回退其他任何记录.4.表现良好.

I am trying to insert 2000 records in Employee table in batch (using mybatis). My requirements are:1. To log the error if any of the record fails to insert.2. To continue with the insertion even if any one of the record fails.3. Rollback should not happen for other if any one of the record fails.4. Good performance.

Dao实现的示例代码:在这里，我提出了两种方案.

Sample code of Dao implementation: Here I have come up with 2 scenarios.

1. 在循环外调用sqlSession.commit().

1. Calling sqlSession.commit() outside the loop.

    SqlSession sqlSession = MyBatisUtil.getSqlSessionFactory().openSession(ExecutorType.BATCH);
    BatchMapper batchMapper = sqlSession.getMapper(BatchMapper.class);
    try
    {
      for(Employee e: empList){
        batchMapper.addEmployee(e);
      }
    }
    catch (Exception ex)
    {

    }
    finally{
        sqlSession.commit();
        sqlSession.close();
    }

在这种情况下，sqlSession.commit()在for循环之外.在调用sqlSession.commit()之后，所有记录都会一次插入.这里的性能很好，插入2000条记录需要4秒钟.但是我无法记录错误，并且在发生异常时也会停止插入.

In this case sqlSession.commit() is outside the for loop. Here insertion is happening for all the records at once after calling sqlSession.commit(). Here the performance is good and it takes 4 seconds to insert 2000 records.But I am not able to log the errors and also it stops the insertion when exception occures.

在循环内调用sqlSession.commit().

Calling sqlSession.commit() inside the loop.

    SqlSession sqlSession = MyBatisUtil.getSqlSessionFactory().openSession(ExecutorType.BATCH);
    BatchMapper batchMapper = sqlSession.getMapper(BatchMapper.class);
    try
    {
     for(Employee e: empList){
        batchMapper.addEmployee(e);
        sqlSession.commit();
      }
    }
    catch (Exception ex)
    {

    }
    finally{
        sqlSession.close();
    }

在这种情况下，sqlSession.commit()位于for循环内.在此，当我们调用sqlSession.commit()时，插入操作会一一进行.此处的性能不佳，插入2000条记录需要10分钟.但是，即使第100条记录发生异常，我也可以记录错误并继续进行插入操作.

In this case sqlSession.commit() is inside the for loop. Here insertion happens one by one when we call sqlSession.commit(). Here the performance is not good and it takes 10 minutes to insert 2000 records.But I am able to log the errors and continue with the insertion even if the exception occures for say 100th record.

请帮助我.预先感谢.

推荐答案

如果您使用的是Java 8，请尝试使用lambda表达式进行并行流.

If you are using java 8, try parallel stream using lambda expressions.

empList.stream().parallel().forEach(s -> {
        try{
            batchMapper.addEmployee(e);
            sqlSession.commit();
        } catch(Exception ex){

        }
    });

这篇关于使用mybatis(3.2.8版)插入2000条记录时出现性能问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！