0

the first thanks for your time.

I am trying to insert data to the database by JPA(spring-boot), the project is using Oracle.

Currently, Insert 5000 record, it takes a long time with repository.save(...) or repository.saveAll(...).

I tried batch_size, but it is not working(looks like it is not working for oracle ?).

Code config:

            Properties properties = new Properties();
            properties.setProperty("hibernate.ddl-auto", "none");
            properties.setProperty("hibernate.dialect", "org.hibernate.dialect.Oracle12cDialect");
            properties.setProperty("hibernate.show_sql", "true");
            properties.put("hibernate.jdbc.batch_size", 5);
            properties.put("hibernate.order_inserts", true);
            properties.put("hibernate.order_updates", true);
            setJpaProperties(properties);

I create sql query to insert several rows at one time execute statement.

INSERT ALL INTO table(...)...

I hope there is a better and more efficient way

So, can you give me any solution?

Thankyou so much!!!!

Vũ Minh Vương
  • 163
  • 6
  • 20
  • Does `batch_size` affect how often changes are committed to the database? If so, "5" is probably far too small. Too many commits will definitely affect your performance. Try a much higher value, like 1000, or even 5000 (i.e. only one commit, at the end), if you can. – pmdba May 12 '22 at 02:56
  • @pmdba batch_size help create one statement can insert serveral row data. Like insert into table(col1, col2) values(1,2) (2,3)... But, I tried and it is not working – Vũ Minh Vương May 12 '22 at 03:01
  • How do you generate ids? Do you start outer transaction when inserting entities? – Andrey B. Panfilov May 12 '22 at 04:10
  • @AndreyB.Panfilov Ids created(based on business), just insert. – Vũ Minh Vương May 13 '22 at 06:11

1 Answers1

0

How about: batch_size : 1000 when entity count is 1000, then :repository.saveAndFlush(); then call the next batch.

Another method can be call the EntityManager persist directly in the batch saving. like:

public int saveDemoEntities(List<DemoEntity> DemoEntities) {
    long start = System.currentTimeMillis();
    int count = 0;
    for (DemoEntities o : DemoEntities) {
        entityManager.persist(o);
        count++;
        if (count % BATCH_COUNT == 0) {
            entityManager.flush();
            entityManager.clear();
        }
    }
    entityManager.flush();
    entityManager.clear();
    return count;
}
SeanH
  • 538
  • 2
  • 8
  • batch_size is not working. It is not generate sql with INSERT ALL INTO...., juts INSERT INTO table(...) values..(). It will take 5000 sql for 5000 records – Vũ Minh Vương May 13 '22 at 06:13
  • Could you try the steps in the topic: https://stackoverflow.com/questions/50772230/how-to-do-bulk-multi-row-inserts-with-jparepository? – SeanH May 13 '22 at 08:17
  • my problem is batch_size was not working. And insert statment for oracle is difference mysql or jdbc:postgresql – Vũ Minh Vương May 17 '22 at 02:45
  • Understand. Perhaps you can call EntityManager directly for the batch save. – SeanH May 17 '22 at 03:43