2

I am using spring boot and spring data jpa with hibernate, japrepository.save(List) took 20 minutes to save 8000 records to oracle database. can some one help me how to solve it. Adding spring.jpa.properties.hibernate.jdbc.batch_size=1000 in application.properties also didn't help.

application.properties 

server.servlet-path = /*         
spring.datasource.jndi-name=jdbc/mydatasource       
spring.datasource.testWhileIdle = true         
spring.datasource.validationQuery = SELECT 1 
spring.jpa.database-platform=org.hibernate.dialect.Oracle10g‌​Dialect 
spring.jpa.properties.hibernate.jdbc.batch_size=1000
ldz
  • 2,217
  • 16
  • 21
user7220859
  • 113
  • 2
  • 2
  • 9
  • Do you have primary key generation with GeneratedValue? And if yes what strategy – Simon Martinelli Jan 29 '18 at 13:50
  • @Id @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "ubseq") @SequenceGenerator(sequenceName = "OWNER.ub_seq", schema = "OWNER", allocationSize = 1, name = "ubseq") private long id; – user7220859 Jan 30 '18 at 04:37
  • The problem could be the allocationSize of your Sequence because for every insert it will get a new sequence number that is a expensive operation. Is there a reason for having it set to 1? – Simon Martinelli Jan 30 '18 at 07:57
  • No specific reason, actually I m not very familiar with Spring/Hibernate frameworks. I need the primary key value from a sequence, or if you could suggest something else will try. – user7220859 Jan 30 '18 at 08:28
  • great, I just removed 'allocationSize = 1' attribute from SequenceGenerator and all 8000 records got saved in 1 min. Initially I thought it is same like increment by one. Now my code is working in Tomcat , I need to actually deploy it on JBoss, lets see how it works. Many thanks Simon Martinelli :) – user7220859 Jan 30 '18 at 08:50
  • Great! I posted this a the answer. Please flag the answer as correct answer. Thank you! – Simon Martinelli Jan 30 '18 at 09:24

2 Answers2

4

The problem could be the allocationSize of your Sequence because for every insert it will get a new sequence number that is a expensive operation.

If you can set the allocationSize to a higher value that works for your case.

Simon Martinelli
  • 34,053
  • 5
  • 48
  • 82
1

Try to use a reasonable batch_size like 50 or 30. In addition to that try to set the parameter

spring.jpa.hibernate.order_inserts=true spring.jpa.hibernate.order_updates=true

So the inserts and updates are ordered before sending to database. Also look here Spring Boot JPA Bulk insert or https://memorynotfound.com/hibernate-jpa-batch-insert-batch-update-example/

For further examples and details.

Francesco Iannazzo
  • 596
  • 2
  • 11
  • 31
  • I tried adding spring.jpa.properties.hibernate.jdbc.batch_size=100 spring.jpa.hibernate.order_inserts=true spring.jpa.hibernate.order_updates=true it alone didn't solve the issue but these configureations also need along removing 'allocation size' from SequenceGenerator. Thanks Francesco – user7220859 Jan 30 '18 at 11:18