2

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?

James Z
  • 12,209
  • 10
  • 24
  • 44
VNR
  • 21
  • 1
  • 4
  • What is your sequence generator configuration? – Lesiak Feb 13 '18 at 11:14
  • Java Config @SequenceGenerator(name = "MY_SEQ_GEN", sequenceName = "MY_SEQ", allocationSize = 1) @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "MY_SEQ_GEN") DB Sequence: CREATE SEQUENCE MY_SEQ MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1; – VNR Feb 13 '18 at 12:32

2 Answers2

5

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/)

Lesiak
  • 22,088
  • 2
  • 41
  • 65
  • What about using a GUID generator? I would think that's the best without needing to grab a sequence at all and not having to wait for insert? – Dean Hiller Aug 15 '20 at 15:31
1

allocationSize=1 is the real issue here. With this configuration hibernate will call nextVal() for each insert so if you have 1000 inserts then hibernate will call nextVal() a 1000 times.

For more information refer to this article by Vlad Mihalcea

Charlie
  • 3,113
  • 3
  • 38
  • 60