Improve insert performance massively

spring-data-jpa bulkinsert
beginner_ picture beginner_ · Nov 13, 2012 · Viewed 15.3k times · Source

In my application I need to massively improve insert performance. Example: A file with about 21K records takes over 100 min to insert. There are reasons it can takes some time, like 20 min or so but over 100 min is just too long.

Data is inserted into 3 tables (many-to-many). Id's are generated from a sequence but I have already googled and set hibernate.id.new_generator_mappings = true and allocationSize + sequence increment to 1000.

Also the amount of data is not anything extraordinary at all, the file is 90 mb.

I have verified with visual vm that most of the time is spent in jdbc driver (postgresql) and hibernate. I think the issue is related to a unique constraint in the child table. The service layer makes a manual check (=SELECT) before inserting. If the record already exists, it reuses it instead of waiting for a constraint exception.

So to sum it up for the specific file there will be 1 insert per table (could be different but not for this file which is the ideal (fastest) case). That means total 60k inserts + 20k selects. Still over 100 min seems very long (yeah hardware counts and it is on a simple PC with 7200 rpm drive, no ssd or raid). However this is an improved version over a previous application (plain jdbc) on which the same insert on this hardware took about 15 min. Considering that in both cases about 4-5 min is spent on "pre-processing" the increase is massive.

Any tips who this could be improved? Is there any batch loading functionality?

Answer

beginner_ picture beginner_ · Feb 18, 2013

see

spring-data JPA: manual commit transaction and restart new one

Add entityManager.flush() and entityManager.clear() after every n-th call to save() method. If you use hibernate add hibernate.jdbc.batch_size=100 which seems like a reasonable choice.

Performance increase was > 10x, probably close to 100x.

Related questions

How do I update an entity using spring-data-jpa?

Well the question pretty much says everything. Using JPARepository how do I update an entity? JPARepository has only a save method, which does not tell me if it's create or update actually. For example, I insert a simple Object to …

Read More
How to use OrderBy with findAll in Spring Data

I am using spring data and my DAO looks like public interface StudentDAO extends JpaRepository<StudentEntity, Integer> { public findAllOrderByIdAsc(); // I want to use some thing like this } In above code, commented line shows my intent. Can spring Data …

Read More
Spring Data JPA - "No Property Found for Type" Exception

Well, I searched Google and found many results, but none of them was able to answer my problem. So, here it goes. I am trying to study Spring MVC and Spring Data JPA by doing a minimal implementation of pinterest …

Read More