0

I am using Spring NamedParameterJdbcTemplate using Hikar datasource using Oracle database.. I have scenario where it makes a select query in a loop for 200 times. It works fine for 75th loop but after that it gets slow and taking lots of time around 2 hrs to complete 200th call.

Please help to solve this problem where exactly the problem is. Below is the Hikari connection pool configuration.


HikariDataSource ds = properties.initializeDataSourceBuilder().type(HikariDataSource.class).build();
        ds.setMaximumPoolSize(1000);
        ds.setIdleTimeout(10000);
        ds.setMaxLifetime(1000);
        ds.setMinimumIdle(10);

Query it is making

select fields from table where COLUMN like  :value order by col1,col2

I think after 75th call, it is not able to use the Connection pool properly and it is taking time to create the connection. Please let me know if any other details are required.

I tried to configure the connection pooling with different values but it could not help lot.

sumit paul
  • 1
  • 1
  • Why in a loop? That sounds wrong already to start with. And if it is in a loop you should use a single transaction so it uses 1 connection. If this is Spring Boot why are you defining your own datasource instead of just letting Spring Boot configure stuff. That and the loop makes me wonder what you are doing in your code actually. – M. Deinum Apr 24 '23 at 06:17

0 Answers0