Batch inserts using JPA EntityManager

Question

Is there a way where we can use batch inserts using JPA EntityManager. I know there is no direct way to achieve this but there must be some way to achieve this mechanism.

Actually, for every insert operation it's taking me 300ms which I want to reduce using batch inserts instead of single inserts.

Here's code I am using currently executing for single inserts

        @PersistenceContext(unitName = "testing")
        EntityManager eM;

        Query querys = this.eM.createNativeQuery(insertQuery);
        for (String s : someList) {
            //setting parameters
            querys.executeUpdate();
        }

Thanks in advance.

Maniple

Answer 1

It is possible to perform batch writes using JPA, but it's highly dependent on the specific implementation of your persistence provider, database and JDBC driver. For example, this article explains how to enable batch writing (optimization #8) using EclipseLink JPA 2.3 and an Oracle database.Look for similar configuration parameters in your particular environment.

Goodson

Hi,can u provide me some code snippet how to use the same in above provided code. – Maniple

@Rana as I stated above: it depends on what persistence provider you're using - and I can't tell by looking at the code, you have to tell me. – Shrubby

Hi,I am using org.eclipse.persistence.jpa.PersistenceProvider. Also please let me know if there are any limitations using the batch inserts. – Maniple

@Rana it's all in the linked article, you have to edit the persistence.xml file and add something like <property name="eclipselink.jdbc.batch-writing" value="JDBC"/> <property name="eclipselink.jdbc.batch-writing.size" value="1000"/>. Please take the time to read the article first. – Shrubby

Hi,I have added that already and I am looking info for any pitfalls I need to watch using this statement. Thanks. – Maniple

Answer 2

I know this is a fairly old question with an accepted answer. Nonetheless, I'd like give a new answer to this very specific subject "JPA batch inserts".

@PersistenceContext
private EntityManager entityManager;
 
@Value("${hibernate.jdbc.batch_size}")
private int batchSize;
 
public <T extends MyClass> Collection<T> bulkSave(Collection<T> entities) {
  final List<T> savedEntities = new ArrayList<T>(entities.size());
  int i = 0;
  for (T t : entities) {
    savedEntities.add(persistOrMerge(t));
    i++;
    if (i % batchSize == 0) {
      // Flush a batch of inserts and release memory.
      entityManager.flush();
      entityManager.clear();
    }
  }
  // Flush one last time to catch those beyond that last full batch.
  entityManager.flush();
  entityManager.clear();
  return savedEntities;
}
 
private <T extends MyClass> T persistOrMerge(T t) {
  if (t.getId() == null) {
    entityManager.persist(t);
    return t;
  } else {
    return entityManager.merge(t);
  }
}

Source: my own blob post at http://frightanic.com/software-development/jpa-batch-inserts/

Haith

I guess we need to again flush() and clear() at the end to save any remaining objects which didn't complete our batch size? – Drone

Answer 3

Depending on whether the transaction encloses the loop, batching typically already happens in your case.

JPA will collect all your updates in its L1 cache, and typically write that all to the DB in a batch when the transaction commits. This is not really that different with batching in JDBC, where every batch-item you add is also temporarily in memory until you call an update method.

Potentially problematic is that you don't have hard guarantees that JPA indeed does this batching at all and if does this at transaction commit or when a threshold is reached, but I found that in practice in nearly all cases and especially in cases involving such a simple update loop, it indeed does batching.

One problem is that even if JPA indeed already does batching you still may want to control batch sizes. The articles linked by the other answers provide pretty useful information for that.

Finally, you should be aware that your L1 cache keeps growing in a loop, so if the number of updates are really large, periodically clear it. Alternatively, if your business logic can sustain it, do partial updates in multiple transactions. E.g. item 0 to 100.000 in transaction 1, 100.001 to 200.000 in transaction 2, etc.

Depositary

Hey I used spring data jpa now, so you mean if I update object in a loop inside a method and this method is marked by @Transactional, it will automatically batch update like jdbc batch update. – Regnal

Answer 4

JPA by itself does not have any settings for batching. However there are some implementation-dependent settings. Here is an example for hibernate.

Contrary

See my example here – Harvison