How to improve spring data JPA performance
Multi tool use
How to improve spring data JPA performance
I am attempting to improve the performance of my application in which one of the operations is to read data from a CSV file and store the values from each row as one POJO (so 1500 CSV rows = 1500 POJOs) in a PostgresSQL database. It is a spring boot application and uses a JpaRepository with (default configurations) as the means for persistence. My original attempt was basically this statement in each iteration of the loop as it read each row in the CSV file:
autowiredRepoInstance.save(objectInstance);
autowiredRepoInstance.save(objectInstance);
However with the spring.jpa.show-sql=true setting in the application.properties file, I saw that there was one insert being done for each POJO. My attempt at improving the performance was to declare an ArrayList outside the loop, save each instance of the POJO in that list within the loop, and at every 500th item, perform a save, as below (ignoring for now the cases where there are more/less than multiples of 500):
spring.jpa.show-sql=true
application.properties
loop(
objList.add(objectInstance);
if (objList.size() == 500) {
autowiredRepoInstance.save(objList);
objList.clear();
}
)
However, this was also generating individual insert statements. What settings can I change to improve performance? Specifically, I would like to minimize the number of SQL statements/operations, and have the underlying Hibernate use "multirow" inserts that postgresql allows:
https://www.postgresql.org/docs/9.6/static/sql-insert.html
But any other suggestions are also welcomed.
Thank you.
The best performance you get if you write your own
INSERT .. SELECT, but an acceptable solution would be to use of Hybernate Batch Insert. This is similar concept to the multirow insert. The optimal batch size you must examine while testing in your environment.– Marmite Bomber
Jul 2 at 15:24
INSERT .. SELECT
You should use batch insert: stackoverflow.com/a/50882952
– Cepr0
Jul 2 at 15:28
@Cepr0 I looked at your answer but for some reason my repo does not have a
saveAll method even though it is also a JpaRepository.– ITWorker
Jul 2 at 15:50
saveAll
JpaRepository
@ITWorker
saveAll() method starts from Spring Boot 2.0. In the prev. version you can use save() method.– Cepr0
Jul 2 at 15:52
saveAll()
save()
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
@a_horse_with_no_name I updated the reference. I am using 9.6.
– ITWorker
Jul 2 at 15:03