spring data saveAll very slow

Question

i m using spring data saveAll to save 3500 records in an Oracle database but it execute very slowly, is there a way to do bulk insert or any other fast way

 noteRepository.saveAll(noteEntityList);//<- this one is slow for 3000 records

thanks in advance

Please use the search as questions regarding JPA and batch inserts have been answered multiple times before. — M. Deinum, Apr 19 '21 at 18:05

score 4 · Accepted Answer · answered Apr 19 '21 at 17:25

4

By default, saveAll does not create batch, the batch processing needs to be enabled. You need to set below properties to enable batch processing

spring.jpa.properties.hibernate.jdbc.batch_size=100
spring.jpa.properties.hibernate.order_inserts=true (if inserts)
OR
spring.jpa.properties.hibernate.order_updates=true (if updates)

First property collects the transaction in batch and second property collects the statements grouped by entity.

Check this thread for more details How to do bulk (multi row) inserts with JpaRepository?

answered Apr 19 '21 at 17:25

pratap

538
1
5
11

1

This is only part of the answer. Eventually it will still slow down due to how JPA works. A regular flush and clear is needed as well in between batches else dirty checking will kick in, which takes longer and longer depending on the amount of entities in the first level cache. – M. Deinum Apr 19 '21 at 18:06

score 2 · Answer 2 · answered Feb 24 '22 at 20:35

Also, if you want to do batch inserts, make sure that if your table has an auto-incremented column (say as a PK), that its set up as a Sequence (not Identity) and that the allocationSize (Java) and increment_by value (DB Sequence) are set to the batch size you are trying to persist. Don't set those values to one, else insert will still be slow as JPA will need to keep going back to the DB to get the next value from the sequence.

score 0 · Answer 3 · answered Aug 18 '23 at 09:46

In my case my ID column was not incremental number, so spring data had to check whether its a update or a insert and then it used to do the insert. My senior confirmed that if you give id as 0 then it indicates its a insert so it wont check for update and it will be faster...

spring data saveAll very slow

3 Answers3