title: Need to insert 100000 rows in mysql using hibernate in under 5 seconds
body: <p>I am trying to insert 100,000 rows in a MYSQL table under 5 seconds using Hibernate(JPA). I have tried every trick hibernate offers and still can not do better than 35 seconds.  </p>
  
  <p>1st optimisation : I started with IDENTITY sequence generator which was resulting in 60 seconds to insert. I later abandoned the sequence generator and started assigning the <code>@Id</code> field myself by reading the <code>MAX(id)</code> and using <code>AtomicInteger.incrementAndGet()</code> to assign fields myself. That reduced the insert time to 35 seconds.</p>
  
  <p>2nd optimisation : I enabled batch inserts, by adding </p>
  
  <p><code>&lt;prop key="hibernate.jdbc.batch_size"&gt;30&lt;/prop&gt;
  &lt;prop key="hibernate.order_inserts"&gt;true&lt;/prop&gt;
  &lt;prop key="hibernate.current_session_context_class"&gt;thread&lt;/prop&gt;
  &lt;prop key="hibernate.jdbc.batch_versioned_data"&gt;true&lt;/prop&gt;</code> </p>
  
  <p>to the configuration. I was shocked to find that batch inserts did absolutely nothing to decrease insert time. It was still 35 seconds!</p>
  
  <p>Now, I am thinking about trying to insert using multiple threads.
  Anyone has any pointers? Should I have chosen MongoDB?</p>
  
  <p>Below is my configuration:
  1. Hibernate configuration
  `</p>
  
  <pre><code>&lt;bean id="entityManagerFactoryBean" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean"&gt;
          &lt;property name="dataSource" ref="dataSource" /&gt;
          &lt;property name="packagesToScan" value="com.progresssoft.manishkr" /&gt;
          &lt;property name="jpaVendorAdapter"&gt;
              &lt;bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter" /&gt;
          &lt;/property&gt;
          &lt;property name="jpaProperties"&gt;
              &lt;props&gt;
                  &lt;prop key="hibernate.hbm2ddl.auto"&gt;${hibernate.hbm2ddl.auto}&lt;/prop&gt;
                  &lt;prop key="hibernate.dialect"&gt;${hibernate.dialect}&lt;/prop&gt;
                  &lt;prop key="hibernate.show_sql"&gt;${hibernate.show_sql}&lt;/prop&gt;
                  &lt;prop key="hibernate.format_sql"&gt;${hibernate.format_sql}&lt;/prop&gt;
                  &lt;prop key="hibernate.jdbc.batch_size"&gt;30&lt;/prop&gt;
                  &lt;prop key="hibernate.order_inserts"&gt;true&lt;/prop&gt;
                  &lt;prop key="hibernate.current_session_context_class"&gt;thread&lt;/prop&gt;
                  &lt;prop key="hibernate.jdbc.batch_versioned_data"&gt;true&lt;/prop&gt;
              &lt;/props&gt;
          &lt;/property&gt;
      &lt;/bean&gt;
  
      &lt;bean class="org.springframework.jdbc.datasource.DriverManagerDataSource"
            id="dataSource"&gt;
          &lt;property name="driverClassName" value="${database.driver}"&gt;&lt;/property&gt;
          &lt;property name="url" value="${database.url}"&gt;&lt;/property&gt;
          &lt;property name="username" value="${database.username}"&gt;&lt;/property&gt;
          &lt;property name="password" value="${database.password}"&gt;&lt;/property&gt;
      &lt;/bean&gt;
  
      &lt;bean id="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager"&gt;
          &lt;property name="entityManagerFactory" ref="entityManagerFactoryBean" /&gt;
      &lt;/bean&gt;
  
  
  
      &lt;tx:annotation-driven transaction-manager="transactionManager" /&gt;
  </code></pre>
  
  <p>`</p>
  
  <ol start="2">
  <li>Entity configuration :</li>
  </ol>
  
  <p>`</p>
  
  <pre><code>@Entity
  @Table(name = "myEntity")
  public class MyEntity {
  
      @Id
      private Integer id;
  
      @Column(name = "deal_id")
      private String dealId;
  
      ....
      ....
  
      @Temporal(TemporalType.TIMESTAMP)
      @Column(name = "timestamp")
      private Date timestamp;
  
      @Column(name = "amount")
      private BigDecimal amount;
  
      @OneToOne(cascade = CascadeType.ALL)
      @JoinColumn(name = "source_file")
      private MyFile sourceFile;
  
      public Deal(Integer id,String dealId, ....., Timestamp timestamp, BigDecimal amount, SourceFile sourceFile) {
          this.id = id;
          this.dealId = dealId;
          ...
          ...
          ...
          this.amount = amount;
          this.sourceFile = sourceFile;
      }
  
  
      public String getDealId() {
          return dealId;
      }
  
      public void setDealId(String dealId) {
          this.dealId = dealId;
      }
  
     ...
  
     ...
  
  
      ....
  
      public BigDecimal getAmount() {
          return amount;
      }
  
      public void setAmount(BigDecimal amount) {
          this.amount = amount;
      }
  
      ....
  
  
      public Integer getId() {
          return id;
      }
  
      public void setId(Integer id) {
          this.id = id;
      }
  </code></pre>
  
  <p>`</p>
  
  <ol start="3">
  <li>Persisting code (service) : </li>
  </ol>
  
  <p>`</p>
  
  <pre><code>@Service
  @Transactional
  public class ServiceImpl implements MyService{
  
      @Autowired
      private MyDao dao;
  ....
  
  `void foo(){
          for(MyObject d : listOfObjects_100000){
              dao.persist(d);
          }
  }
  </code></pre>
  
  <p>`
  4. Dao class : </p>
  
  <p>`</p>
  
  <pre><code>@Repository
  public class DaoImpl implements MyDao{
  
      @PersistenceContext
      private EntityManager em;
  
      public void persist(Deal deal){
          em.persist(deal);
      }
  }
  </code></pre>
  
  <p>`</p>
  
  <p>Logs:
  `</p>
  
  <pre><code>DEBUG o.h.e.j.b.internal.AbstractBatchImpl - Reusing batch statement
  18:26:32.906 [http-nio-8080-exec-2] DEBUG org.hibernate.SQL - insert into deal (amount, deal_id, timestamp, from_currency, source_file, to_currency, id) values (?, ?, ?, ?, ?, ?, ?)
  18:26:32.906 [http-nio-8080-exec-2] DEBUG o.h.e.j.b.internal.AbstractBatchImpl - Reusing batch statement
  18:26:32.906 [http-nio-8080-exec-2] DEBUG org.hibernate.SQL - insert into deal (amount, deal_id, timestamp, from_currency, source_file, to_currency, id) values (?, ?, ?, ?, ?, ?, ?)
  18:26:32.906 [http-nio-8080-exec-2] DEBUG o.h.e.j.b.internal.AbstractBatchImpl - Reusing batch statement
  18:26:32.906 [http-nio-8080-exec-2] DEBUG org.hibernate.SQL - insert into deal (amount, deal_id, timestamp, from_currency, source_file, to_currency, id) values (?, ?, ?, ?, ?, ?, ?)
  18:26:32.906 [http-nio-8080-exec-2] DEBUG o.h.e.j.b.internal.AbstractBatchImpl - Reusing batch statement
  18:26:32.906 [http-nio-8080-exec-2] DEBUG org.hibernate.SQL - insert into deal (amount, deal_id, timestamp, from_currency, source_file, to_currency, id) values (?, ?, ?, ?, ?, ?, ?)
  18:26:32.906 [http-nio-8080-exec-2] DEBUG o.h.e.j.b.internal.AbstractBatchImpl - Reusing batch statement
  18:26:32.906 [http-nio-8080-exec-2] DEBUG org.hibernate.SQL - insert into deal (amount, deal_id, timestamp, from_currency, source_file, to_currency, id) values (?, ?, ?, ?, ?, ?, ?)
  18:26:32.906 [http-nio-8080-exec-2] 
  </code></pre>
  
  <p>...
  ...</p>
  
  <pre><code>DEBUG o.h.e.j.b.internal.AbstractBatchImpl - Reusing batch statement
  18:26:34.002 [http-nio-8080-exec-2] DEBUG org.hibernate.SQL - insert into deal (amount, deal_id, timestamp, from_currency, source_file, to_currency, id) values (?, ?, ?, ?, ?, ?, ?)
  18:26:34.002 [http-nio-8080-exec-2] DEBUG o.h.e.j.b.internal.AbstractBatchImpl - Reusing batch statement
  18:26:34.002 [http-nio-8080-exec-2] DEBUG org.hibernate.SQL - insert into deal (amount, deal_id, timestamp, from_currency, source_file, to_currency, id) values (?, ?, ?, ?, ?, ?, ?)
  18:26:34.002 [http-nio-8080-exec-2] DEBUG o.h.e.j.b.internal.AbstractBatchImpl - Reusing batch statement
  18:26:34.002 [http-nio-8080-exec-2] DEBUG org.hibernate.SQL - insert into deal (amount, deal_id, timestamp, from_currency, source_file, to_currency, id) values (?, ?, ?, ?, ?, ?, ?)
  18:26:34.002 [http-nio-8080-exec-2] DEBUG o.h.e.j.b.internal.AbstractBatchImpl - Reusing batch statement
  18:26:34.002 [http-nio-8080-exec-2] DEBUG org.hibernate.SQL - insert into deal (amount, deal_id, timestamp, from_currency, source_file, to_currency, id) values (?, ?, ?, ?, ?, ?, ?)
  18:26:34.002 [http-nio-8080-exec-2] DEBUG o.h.e.j.batch.internal.BatchingBatch - Executing batch size: 27
  18:26:34.011 [http-nio-8080-exec-2] DEBUG org.hibernate.SQL - update deal_source_file set invalid_rows=?, source_file=?, valid_rows=? where id=?
  18:26:34.015 [http-nio-8080-exec-2] DEBUG o.h.e.j.batch.internal.BatchingBatch - Executing batch size: 1
  18:26:34.018 [http-nio-8080-exec-2] DEBUG o.h.e.t.i.jdbc.JdbcTransaction - committed JDBC Connection
  18:26:34.018 [http-nio-8080-exec-2] DEBUG o.h.e.t.i.jdbc.JdbcTransaction - re-enabling autocommit
  18:26:34.032 [http-nio-8080-exec-2] DEBUG o.s.orm.jpa.JpaTransactionManager - Closing JPA EntityManager [org.hibernate.jpa.internal.EntityManagerImpl@2354fb09] after transaction
  18:26:34.032 [http-nio-8080-exec-2] DEBUG o.s.o.jpa.EntityManagerFactoryUtils - Closing JPA EntityManager
  18:26:34.032 [http-nio-8080-exec-2] DEBUG o.h.e.j.internal.JdbcCoordinatorImpl - HHH000420: Closing un-released batch
  18:26:34.032 [http-nio-8080-exec-2] DEBUG o.h.e.j.i.LogicalConnectionImpl - Releasing JDBC connection
  18:26:34.033 [http-nio-8080-exec-2] DEBUG o.h.e.j.i.LogicalConnectionImpl - Released JDBC connection
  </code></pre>
  
  <p>'</p>