Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Batch inserts using JPA EntityManager

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.

like image 373
Ran Avatar asked May 14 '12 13:05

Ran


People also ask

What is batch insert?

Batch inserts is the ability to send a set of inserts to a single table, once to the database as a single insert statement instead of individual statements. This method improves latency, and data insert times.

What is batch update in Hibernate?

1. Overview. In this tutorial, we'll learn how we can batch insert and update entities using Hibernate/JPA. Batching allows us to send a group of SQL statements to the database in a single network call. This way, we can optimize the network and memory usage of our application.


1 Answers

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.

like image 73
Arjan Tijms Avatar answered Oct 04 '22 08:10

Arjan Tijms