0

I had a piece of code which was inserting data into 4 tables in DB one record at a time. The data comes from a file which has around 11K records on an average. With this code the complete processing is getting done in 10-15 min.

for (final LedgerEntry ledgerEntry : items) {
    final EntityTransaction tx = entityManager.getTransaction();
    tx.begin(); 
    entityManager.persist(ledgerEntry);
    tx.commit();
}

I have refactored the code to insert the data in one go so that if something goes wrong no data is inserted in DB which was not the case in above piece of code. My new code is taking around 10 min to insert data in DB in test env. but in Live it was working very slow and took around 6-7 hours for 9k records.

final EntityTransaction tx = entityManager.getTransaction();
if (!tx.isActive()) tx.begin();
for (final LedgerEntry ledgerEntry : items) {
    entityManager.persist(ledgerEntry);
}
tx.commit();

Can anyone suggest what can be done to increase the performance as the earlier code is causing lot of issues incase file fails and inserts duplicate records

Dragan Bozanovic
  • 23,102
  • 5
  • 43
  • 110
Rachit M Garg
  • 263
  • 4
  • 19
  • Sounds like a perfect use case for [batch inserts](http://stackoverflow.com/questions/35791383/spring-data-jpa-batch-insert-for-nested-entities). – Dragan Bozanovic Sep 12 '16 at 15:25
  • But as it is said "A JDBC batch can target one table only, so every new DML statement targeting a different table ends up the current batch and initiates a new one. Mixing different table statements is therefore undesirable when using SQL batch processing.", Here in my case one record will be inserting data in around 4 tables, so will the batch processing make a difference? – Rachit M Garg Sep 13 '16 at 09:48
  • Take a look at linked post again. There are parameters which enable sorting of insert statements, so that successive statements deal with the same table. – Dragan Bozanovic Sep 13 '16 at 10:19
  • so this is how I am adding the properties @DraganBozanovic protected Properties hibernateProperties() {return new Properties() {private static final long serialVersionUID = 1L;{setProperty("hibernate.dialect", environment.getProperty("pdc.fre.staging.hibernate.dialect"));setProperty("hibernate.globally_quoted_identifiers", "true");setProperty("hibernate.jdbc.batch_versioned_data", "true");setProperty("hibernate.jdbc.batch_size", "500"); setProperty("hibernate.order_inserts", "true"); setProperty("hibernate.order_updates", "true"); } }; } – Rachit M Garg Sep 13 '16 at 11:50
  • Also we are using MySQL as DB and all the 4 tables have AUTO INCREMENT as Primary Key. – Rachit M Garg Sep 14 '16 at 07:54

0 Answers0