title: MyBatis Performance of bulk operator
body: <p>I have used MyBatis-spring + Java. I need to insert >10000 records into table in one transaction. To do it, I have used a mapper:</p>
  
  <pre><code>&lt;insert id="saveBulk" parameterType="List"&gt;
      INSERT INTO "quote" ("id", "mi_id", "timestamp", "open", "close", "low", "high", "volume", "period")
      VALUES
      &lt;foreach collection="list" item="item" separator=","&gt;
          ( #{item.key}, #{item.marketInstrumentKey}, #{item.timestamp}, #{item.open}, #{item.close}, #{item.low},
          #{item.high}, #{item.volume}, #{item.period}::quote_period)
      &lt;/foreach&gt;
  &lt;/insert&gt;
  </code></pre>
  
  <p>And pass a List to this statement. It is worked extremely slowly for 2000-3000 records, but the 10000 records are inserted more than 4 minutes (I should increase the timeout interval)! The same 10000 records are inserted to the same DB via PgAdmin for less than 10 second. I tried to trace the processing  of this operation and found a bottleneck</p>
  
  <pre><code>public int doUpdate(MappedStatement ms, Object parameter) throws SQLException {
      Statement stmt = null;
      try {
        Configuration configuration = ms.getConfiguration();
        StatementHandler handler = configuration.newStatementHandler(this, ms, parameter, RowBounds.DEFAULT, null, null);
        stmt = prepareStatement(handler, ms.getStatementLog());
        return handler.update(stmt);
      } finally {
        closeStatement(stmt);
      }
    }
  </code></pre>
  
  <p>The StatementHandler is calculated few minutes, and few minutes for prepareStatement. 
  I understand, why it happens: 10000 records with 9 field for each record. All these 100k fields should be inserted as parameters into statement.
  And how can I accelerate this process?</p>
  
  <p>UPDATE:</p>
  
  <p>I implements a batch save with using of "BATCH" mode of sqlFactory and @Transactional. 
  This is configuration of mybatis-spring XML config: </p>
  
  <pre><code>    &lt;bean id="sqlBatchTemplate" class="org.mybatis.spring.SqlSessionTemplate"&gt;
          &lt;constructor-arg index="0" ref="sqlSessionFactory"/&gt;
          &lt;constructor-arg index="1" value="BATCH"/&gt;
      &lt;/bean&gt;
  
      &lt;bean id="quoteBatchMapper" class="org.mybatis.spring.mapper.MapperFactoryBean"&gt;
          &lt;property name="mapperInterface" value="tafm.dataaccess.mybatis.mapper.QuoteMapper"/&gt;
          &lt;property name="sqlSessionFactory" ref="sqlSessionFactory"/&gt;
          &lt;property name="sqlSessionTemplate" ref="sqlBatchTemplate"/&gt;
      &lt;/bean&gt;
     &lt;bean id="dataAccessBatch" class="tafm.dataaccess.DataAccess"&gt;
          &lt;property name="quoteMapper" ref="quoteBatchMapper"/&gt;
      &lt;/bean&gt;
  </code></pre>
  
  <p>Then, I have implemented a "batch" method:</p>
  
  <pre><code>   @Transactional
      public void saveBulk(List&lt;Quote&gt; quotes) {
  
  
          for(Quote q:quotes) {
              mapper.save(q);
          }
      }
  </code></pre>
  
  <p><strong>mapper</strong> - is XML mapper for entity Quote:</p>
  
  <pre><code>&lt;insert id="saveBulk" parameterType="List"&gt;
      INSERT INTO "quote" ("id", "mi_id", "timestamp", "open", "close", "low", "high", "volume", "period")
      VALUES
      &lt;foreach collection="list" item="item" index="index" separator=","&gt;
          ( #{item.key}, #{item.marketInstrumentKey}, #{item.timestamp}, #{item.open}, #{item.close}, #{item.low},
          #{item.high}, #{item.volume}, #{item.period}::quote_period)
      &lt;/foreach&gt;
  &lt;/insert&gt;
  </code></pre>
  
  <p>It is working fast</p>