I have Springboot JPA integration with 2 databases. One is Oracle and the other is postgres 42.6.

I am fetching data from rest-api with 500 records in each request and saving it in postgres. Mostly the data is update and few are inserts.

Currently it is taking around 22 seconds to save the 500 records in postgres.
My application.properties:

spring.datasource.secondary.jndi-name=java:/datasources/IntegratorMasterdataDS
spring.datasource.secondary.driver-class-name=oracle.jdbc.OracleDriver

spring.jpa.secondary.hibernate.dialect = org.hibernate.dialect.OracleDialect


spring.datasource.primary.jndi-name=java:/datasources/ItghPostgresXADS
spring.datasource.primary.driver-class-name=org.postgresql.Driver

I am doing configuration as below:

@Primary
@Configuration
@EnableJpaRepositories(basePackages = "com.tietoevry.absolutesoftware.itgh", entityManagerFactoryRef = "itghEntityManager", transactionManagerRef = "itghTransactionManager")
public class ItghAutoConfiguration {

    @Value("${spring.datasource.primary.jndi-name}")
    private String itghJndiDatasourceName;

    @Primary
    @Bean
    public DataSource itghDataSource() {
        JndiDataSourceLookup jndiDataSourceLookup = new JndiDataSourceLookup();
        return jndiDataSourceLookup.getDataSource(itghJndiDatasourceName);
    }

    @Primary
    @Bean
    public PlatformTransactionManager itghTransactionManager() {
        JpaTransactionManager jpaTransactionManager = new JpaTransactionManager();
        jpaTransactionManager.setEntityManagerFactory(itghEntityManager().getObject());
        
        return jpaTransactionManager;
    }

    @Primary
    @Bean
    public LocalContainerEntityManagerFactoryBean itghEntityManager() {
        LocalContainerEntityManagerFactoryBean factoryBean = new LocalContainerEntityManagerFactoryBean();
        factoryBean.setDataSource(itghDataSource());
        factoryBean.setPackagesToScan("com.tietoevry.absolutesoftware.itgh");
        factoryBean.setJpaVendorAdapter(new HibernateJpaVendorAdapter());
        
        Map<String,Object> jpaProperties = new HashMap<>();
        jpaProperties.put("hibernate.jdbc.batch_size", 500);
        jpaProperties.put("hibernate.show_sql", true);
        jpaProperties.put("hibernate.generate_statistics", true);

        factoryBean.setJpaPropertyMap(jpaProperties);
        return factoryBean;
    }
}

for oracle database i am doing same way but without jpaProperties and making it secondary db.

@Repository
public interface ApplicationsRepository extends JpaRepository<Applications, String> {}

After add the 500 records in List i am calling

appRepository.saveAll(applications);
appRepository.flush();

Hibernate statistics: As per stats it shows that 70 batches were created but the sql are printed for each select query and each update/insert there is not batching. And it is taking same time if i am removing “batch_size” from jpaProperties.

Metrics {
    45644216 nanoseconds spent acquiring 1 JDBC connections;
    0 nanoseconds spent releasing 0 JDBC connections;
    950436647 nanoseconds spent preparing 18711 JDBC statements;
    851450529369 nanoseconds spent executing 18641 JDBC statements;
    14912223124 nanoseconds spent executing 70 JDBC batches;
    0 nanoseconds spent performing 0 L2C puts;
    0 nanoseconds spent performing 0 L2C hits;
    0 nanoseconds spent performing 0 L2C misses;
    18845894086 nanoseconds spent executing 39 flushes (flushing a total of 388757 entities and 0 collections);
    0 nanoseconds spent executing 0 partial-flushes (flushing a total of 0 entities and 0 collections)
}

I am missing something which is not doing the batch insert/update properly.