title: High CPU utilization when doing thousands of inserts with Hibernate
body: <p>We have recently implemented DB binding using Hibernate with EntityManager (no Spring) to write records to a database.  To make things simple, I will only discuss the variation of the process that only does inserts.  (Another, very similar, process updates an existing record once to set a status, but otherwise, just inserts a bunch of records.)</p>
  
  <p>This process can insert as many as 10,000 records per transaction, though the average is less than that, probably by at least half.  We might have a few instances of this process running in different threads at the same time under the same JVM.</p>
  
  <p>We had a production issue where the service the process was running under was pegging out all 24 cores on the machine.  (They added 12 just to try to accommodate this.)  We have narrowed this high utilization down to Hibernate.</p>
  
  <p>I have spent days researching and can not find anything that might improve our performance, except to use hibernate.jdbc.batch_size along with hibernate.order_inserts.  Unfortunately, we are using IDENTITY as our generation strategy, so Hibernate can/will not batch those inserts.</p>
  
  <p>I've spent days researching and have not found any other performance tip when doing large numbers of inserts.  (I've seen many tips regarding reads, updates, and deletes, but very few for inserts.)</p>
  
  <p>We have a root JobPO object.  We simply call merge on that and all the inserts are handled via the cascade annotations.  We need to do this in a single transaction.  </p>
  
  <p>We have only 8 different tables that we are inserting into, but the hierarchy of records is a bit complicated.</p>
  
  <pre><code>public void saveOrUpdate(Object dataHierarchyRoot) {
      final EntityManager entityManager = entityManagerFactory.createEntityManager();
      final EntityTransaction transaction = entityManager.getTransaction();
  
      try {
          transaction.begin();
  
          // This single call may result in inserting up to 10K records
          entityManager.merge(dataHierarchyRoot);
          transaction.commit();
      } catch (final Throwable e) {
          // error handling redacted for brevity
      } finally {
          entityManager.close();
      }
  }
  </code></pre>
  
  <p>We create the EntityManagerFactory only once.</p>
  
  <p>Any ideas?</p>
  
  <p>Additional notes:</p>
  
  <ul>
  <li><p>No one has complained about the process using too much memory</p></li>
  <li><p>For the variation of the process that is doing only inserts, we could just use "persist" instead of "merge".  We are sharing the code, so we do a merge.  I tried switching to persist with no discernible improvement.</p></li>
  <li><p>We do have annotations that result in bi-directional Cascade on a few of the fields.  I tried removing these, but being new to Hibernate, couldn't get it to save properly.  From what I understand, though, it doesn't seem as if this would cause performance degradation for inserts.  I am not using explicit "inverse" settings, since that seems to not matter for inserts, either.  I am a little shaky on both these areas, though.  Is there room for improvement in this area?</p></li>
  <li><p>We ran SQL Profiler during a single transaction.  Nothing seemed amiss and I did not spot room for improvement.  (There were a large number of exec sp_prepexec statements, approximately the same number as the number of records that were inserted.  That was all that was reported.)</p></li>
  <li><p>The code exhibiting this behavior in production was making an explicit call to entityManager.flush() just before the commit().  I removed that code in my local environment.  It did not make a noticeable improvement, but I won't add it back, because there is no reason for us to call flush().</p></li>
  </ul>