Hello
Dear Devloper Team of Hibernate，
Recently when we code our new Function of a Spring Boot Application we have encoute an problem that when we use saveAll() function to save an Iterable List we found its slowly, because there are sequence in the ID as primary key,they will first select nextval from dual(like select seq.name.nextval from dual) to get the next number of sequence,the procedure will cost 150 s,but we need to finish insert 3000 lines in 30 seconds.Could you tell me how to bypass the select sequence procedure and then finish these operation if we wouldn’t want to write the native
SQL?
Thanks a lot;
You should use an allocationSize which is greater than 1.
@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "USERS_SEQ")
@SequenceGenerator(name = "USERS_SEQ", sequenceName = "SEQUENCE_USERS", allocationSize=50)
private Long id;
Make sure the allocationSize is the same in the DB sequence and the Hibernate Sequence generator.
Check out this article for more details.
1 Like
Mr Vlad,Thanks,In fact,my teammate using your function，namely using allocationSize >1 to solve our problem,but there are some confusion,the allocationsize isnt means when i select nextval from dual to get the sequence,it will increase the allocationSizeNum?if i first insert a data column,the ID is 1,how can i get the next ID is 50?
You need to change the INCREMENT BY attributes for the sequence:
ALTER SEQUENCE customers_seq INCREMENT BY 50;sorry,there was an problem,beacuse at first we changed the sequence’s increment by to 50,or there will encouter an problem:
when we change the increment to 1 there will be a problem:
Caused by: javax.persistence.EntityExistsException: A different object with the same identifier value was already associated with the session : [com.travelsky.pss.react.asrp.nsrp.domain.TNsrpFlight#52432]
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:118)
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:157)
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:164)
at org.hibernate.internal.SessionImpl.firePersist(SessionImpl.java:789)
at org.hibernate.internal.SessionImpl.persist(SessionImpl.java:767)
at sun.reflect.GeneratedMethodAccessor37.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.springframework.orm.jpa.SharedEntityManagerCreator$SharedEntityManagerInvocationHandler.invoke(SharedEntityManagerCreator.java:304)
at com.sun.proxy.$Proxy96.persist(Unknown Source)
at org.springframework.data.jpa.repository.support.SimpleJpaRepository.save(SimpleJpaRepository.java:490)
at org.springframework.data.jpa.repository.support.SimpleJpaRepository.saveAll(SimpleJpaRepository.java:522)
at org.springframework.data.jpa.repository.support.SimpleJpaRepository.saveAll(SimpleJpaRepository.java:74)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.springframework.data.repository.core.support.RepositoryComposition$RepositoryFragments.invoke(RepositoryComposition.java:377)
at org.springframework.data.repository.core.support.RepositoryComposition.invoke(RepositoryComposition.java:200)
at org.springframework.data.repository.core.support.RepositoryFactorySupport$ImplementationMethodExecutionInterceptor.invoke(RepositoryFactorySupport.java:629)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:593)
at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:578)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:59)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:294)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139)
This is our current sequence configuration in DB
If you want to change the increment size from 50 to 1, you need to recreate the sequence as identifier conflicts will occur.
But then, the whole point of your question was to use an increment size of 1.
Nevertheless, it’s improbable that your batch is really slow because of the sequence. Maybe you are lacking JDBC batching, or you are fetching too much data. Check out this article for more details.
If you do not want to set the INCREMENT BY value you can give this project a try:
Oh,sorry,I just see it,i will try it.Thanks