amrutprabhu / spring-boot-jpa-bulk-insert-performance

Spring Boot: JPA Bulk Database Insert

In this project, I achieved reducing 10k records insertion time from 183 seconds to just 5 secs.

For this I did teh following changes :-

1) Change the number of records while inserting.

1. Set hibernate batchin insert size with the folowing properties.

   spring.jpa.properties.hibernate.jdbc.batch_size=30

ii. Add connection string properties.

cachePrepStmts=true
&useServerPrepStmts=true
&rewriteBatchedStatements=true

e.g
jdbc:mysql://localhost:3306/BOOKS_DB?serverTimezone=UTC&cachePrepStmts=true&useServerPrepStmts=true&rewriteBatchedStatements=true

iii. Changed the code for inserting, so that saveAll methods get batch sizes of 30 to insert as per what we also set in the properties file.

A very crude implementation of something like this.

for (int i = 0; i < totalObjects; i = i + batchSize) {
if( i+ batchSize > totalObjects){
List<Book> books1 = books.subList(i, totalObjects - 1);
repository.saveAll(books1);
break;
}
List<Book> books1 = books.subList(i, i + batchSize);
repository.saveAll(books1);
}

This reduced the time by not that much, but dropped from 185 secs to 153 Secs. That’s approximately 18% improvement.

2) Change the ID generation strategy.

This made a major impact.

I stopped usign the @GeneratedValue annotation with strategy i.e GenerationType.IDENTITY on my entity class. Hibernate has disabled batch update with this strategy, Because it has to make a select call to get the id from the database to insert each row.

I changed the strategy to SEQUENCE and provided a sequence generator.

public class Book {
@Id
@GeneratedValue(strategy = SEQUENCE, generator = "seqGen")
@SequenceGenerator(name = "seqGen", sequenceName = "seq", initialValue = 1)
private Long id;
}

This change drastically changed the insert performance as Hibernate was able to leverage bulk insert. From the previous performance improvement of 153 secs, the time to insert 10k records reduced to only 9 secs. Thats an increase in performance by nearly 95%.

Next, I pushed it further to use higher batch sizes and I noticed that doubling the batch size does not double down on time. The time to insert only gradually reduces.

Batch Size	Time to insert (Secs)
30	9.5
60	6.48
200	5.04
500	4.46
1000	4.39
2000	4.5
5000	5.09

The most optimal I found for my case was a batch size of 1000 which took around 4.39 secs for 10K records. After that, I saw the performance degrading as you can see in the graph.

spring-boot-jpa-bulk-insert-performance's Issues

Do not use @Data for JPA Entities

spring-boot-jpa-bulk-insert-performance/src/main/java/com/amrut/prabhu/bulkdatainsert/Book.java

Line 10 in f9af79e

@Data

You shouldn't use lombok's @Data annotation in case of JPA entities.
In the case of entities, two entities are the same iff their @Ids are the same! Lombok is not aware of that, because it generates primitive equals() and hashcode() methods.

How to run this project

Hi, this is a great tutorial. Could you provide some steps on how to run this project, like

1. I see you have added the docker-compose.yaml file, so what commands to run it and from which path would this install MySQL database?
2. If docker doesn't install a MySQL instance, and if I don't have one, should I install one and create a database and table manually?
3. Finally, how to run this

Thanks