Performance issue while inserting 2000 records using mybatis(3.2.8 version)

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.

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

  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();
        }
    

    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.

  2. 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();
        }
    

    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.

Please help me with this. Thanks in advance.

Answers


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){

        }
    });

Need Your Help

Fancybox2: horizontal transitions instead of vertical - how?

fancybox

In the examples page when you search (CTRL+F) for image gallery and click the first image, there's this image gallery which you can browse with next/prev arrows. The animation between them is verti...

Combobox with JPEG images

c++ image visual-c++ visual-studio-2012 combobox

I've an old Visual C++ program where a Owner Draw Combobox is filled with bitmap images.