Spring data JPA batch insert is very slow

Question

I am trying to read Excel file with 700K+ records and batch insert those in MySQL database table.

Please note, Excel parsing is fast and I can get my entity objects in an ArrayList within 50 seconds or so.

I am using Spring Boot and Spring Data JPA.

Below is my partial application.properties file:

hibernate.jdbc.batch_size=1000
spring.jpa.hibernate.use-new-id-generator-mappings=true

and my partial Entity class:

@Entity
@Table(name = "WHT_APPS", schema = "TEST")
public class WHTApps {

    @Id
    @TableGenerator(name = "whtAppsGen", table = "ID_GEN", pkColumnName = "GEN_KEY", valueColumnName = "GEN_VAL")
    @GeneratedValue(strategy = GenerationType.TABLE, generator = "whtAppsGen")
    private Long id;

    @Column(name = "VENDOR_CODE")
    private int vendorCode;
    .
    .
    .
    .

Below is my DAO:

@Repository
@Transactional
public class JapanWHTDaoImpl implements JapanWHTDao {

    @Autowired
    JapanWHTAppsRepository appsRepo;

    @Override
    public void storeApps(List<WHTApps> whtAppsList) {
        appsRepo.save(whtAppsList);

    }

and below is Repository class:

@Transactional
public interface JapanWHTAppsRepository extends JpaRepository<WHTApps, Long> {

}

Can someone please enlighten me as to what I am doing incorrect here?

EDIT:

Process does not finish and throws error eventually:-

2017-08-15 15:15:24.516  WARN 14710 --- [tp1413491716-17] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 0, SQLState: 08S01
2017-08-15 15:15:24.516 ERROR 14710 --- [tp1413491716-17] o.h.engine.jdbc.spi.SqlExceptionHelper   : Communications link failure

The last packet successfully received from the server was 107,472 milliseconds ago.  The last packet sent successfully to the server was 107,472 milliseconds ago.
2017-08-15 15:15:24.518  INFO 14710 --- [tp1413491716-17] o.h.e.j.b.internal.AbstractBatchImpl     : HHH000010: On release of batch it still contained JDBC statements
2017-08-15 15:15:24.525  WARN 14710 --- [tp1413491716-17] c.m.v.c3p0.impl.DefaultConnectionTester  : SQL State '08007' of Exception tested by statusOnException() implies that the database is invalid, and the pool should refill itself with fresh Connections.

com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Communications link failure during rollback(). Transaction resolution unknown.
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:1.8.0_131]
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[na:1.8.0_131]
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:1.8.0_131]
    at java.lang.reflect.Constructor.newInstance(Constructor.java:423) ~[na:1.8.0_131]
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:425) ~[mysql-connector-java-5.1.43.jar:5.1.43]
    .
    .
    .
    .
    2017-08-15 15:15:24.526  WARN 14710 --- [tp1413491716-17] c.m.v2.c3p0.impl.NewPooledConnection     : [c3p0] A PooledConnection that has already signalled a Connection error is still in use!
2017-08-15 15:15:24.527  WARN 14710 --- [tp1413491716-17] c.m.v2.c3p0.impl.NewPooledConnection     : [c3p0] Another error has occurred [ com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Communications link failure during rollback(). Transaction resolution unknown. ] which will not be reported to listeners!

com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Communications link failure during rollback(). Transaction resolution unknown.
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:1.8.0_131]

Thanks

Answer 1

I would point one more thing. The problem could be not only hibernate but DB.

When you insert 700k objects in one transaction it could be stored in DB's rollback segment waiting for the transaction commit.

If possible split the logic to have commits in the middle.

Create 1k sized sublists from the main list, save the sublists and commit after each sublist saving.