0

I have an Spring boot application that runs several services and uses oracle database. The database is maintained properly, indexes also added up, and when executing SQL statements directly on SQL Developer, it's getting executed in milliseconds.

In the spring boot, I use this to execute the statement:

Session session = sessionFactory.getCurrentSession();
session.createQuery("from Table where id = :id and status = 0").setParameter("id", id);

Here is the config properties for the database:

spring.jpa.properties.hibernate.enable_lazy_load_no_trans=true
hibernate.hbm2ddl.auto=none

Here is the way of datasource initialization in the datasource config:

    @Bean
    public DataSource dataSource() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource(url, name, pw);
        dataSource.setDriverClassName(...);
        return dataSource;
    }

Recently, it takes so much time to acquire the database connection, it can go up to 10 seconds jut for acquiring connection. I don't think there is any problem in the query. As from the database side, it's also ok. The resources of server which running this service and the database server are also fine, as well as the network. The servers also have auto-scale feature to create new instance when the memory getting low. I just can't figure out what should I do to improve the acquisition time. Could you please help?

Dhana D.
  • 1,670
  • 3
  • 9
  • 33
  • 1
    You should be using a connection pool with pre-acquired connections. Next to that you are comparing apples and oranges, as executing a query in sql developer doesn't entail making a connection, parsing the result and making it into objects, dirty checking etc. etc. – M. Deinum Dec 24 '22 at 08:27
  • What is pre-acquired connection? @M.Deinum – Dhana D. Dec 24 '22 at 08:49
  • 2
    The use of a connection pool (like HIkariCP by default in Spring Boot). Don't use things like `DriverManagerDataSource` in production. – M. Deinum Dec 24 '22 at 14:12
  • Can you tell me why don't? @M.Deinum. In this thread: https://stackoverflow.com/questions/15198319/why-do-we-use-a-datasource-instead-of-a-drivermanager, it even said that when we would like to set up the pool, DriverManager thing could be an option. – Dhana D. Jan 02 '23 at 07:49
  • `DriverManagerDataSource` isn't a connection pool, so trying to use that as a pool simply won't work. – M. Deinum Jan 02 '23 at 08:30
  • Ok. I updated my question, can you please give a detailed explanatatory answer? @M.Deinum – Dhana D. Jan 02 '23 at 08:39
  • As stated use a connection pool... Which is used by default by Spring Boot your configuration disables that part of the auto configuration. Also the fact that you are using plain hibernate, instead of JPA, indicates you are bypassing probably much more auto-configuration and optimizations. – M. Deinum Jan 02 '23 at 08:44

0 Answers0