Home Best JPA ID Generation Strategy for Batch Insertion
Reply: 1

Best JPA ID Generation Strategy for Batch Insertion

VNR
1#
VNR Published in 2018-02-13 10:10:13Z

I am facing an issue while inserting 100 000 records at once using spring data JPA repository. When we execute repo.save(List<Objs>) it is taking a lot of time if we use Sequence generator as it queries the database for the nextval. I am using Oracle, which ID generation is best here?

Lesiak
2#
Lesiak Reply to 2018-02-13 12:52:27Z

Sequence generator is probably a good choice, but you have to tweak its parameters.

In your particular case, I'd start experimenting with allocation size, and then with strategy. See for example: JPA/Hibernate bulk inserts slow

Take a look at the optimizers configuration: https://vladmihalcea.com/hibernate-hidden-gem-the-pooled-lo-optimizer/

Note that your configuration resolves to:

  • SequenceHiLoGenerator on Hibernate 4
  • SequenceStyleGenerator on Hibernate 5, (it has hibernate.id.new_generator_mappings set to true)

You cannot use identity generator (see Hibernate disabled insert batching when using an identity identifier generator)

Table generator is not the best performant one (https://vladmihalcea.com/why-you-should-never-use-the-table-identifier-generator-with-jpa-and-hibernate/)

Additionally, make sure that the number of nextval() is the actual problem. Maybe changing batch size or statement ordering will help (see https://vladmihalcea.com/how-to-batch-insert-and-update-statements-with-hibernate/)

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.29861 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO