DEV Community

Utab Poudel
Utab Poudel

Posted on

Slow JPA execution with micronaut

I implemented micronaut data jpa along with hikari pool.
I'm trying to execute a simple jpa select query for which the total execution time is around 400-800ms.
The query method is-
List findAllByCodeAndItemIdAndItemClass(String code, String itemId, String itemClass);

The execution metrics of the above jpa query is as below -
14:57:55.820 [default-nioEventLoopGroup-1-5] INFO o.h.e.i.StatisticalLoggingSessionEventListener - Session Metrics {
1373200 nanoseconds spent acquiring 1 JDBC connections;
22600 nanoseconds spent releasing 1 JDBC connections;
89600 nanoseconds spent preparing 1 JDBC statements;
402081800 nanoseconds spent executing 1 JDBC statements;
0 nanoseconds spent executing 0 JDBC batches;
0 nanoseconds spent performing 0 L2C puts;
0 nanoseconds spent performing 0 L2C hits;
0 nanoseconds spent performing 0 L2C misses;
0 nanoseconds spent executing 0 flushes (flushing a total of 0 entities and 0 collections);
3600 nanoseconds spent executing 1 partial-flushes (flushing a total of 0 entities and 0 collections)
}
Result Found: 1
====> Execution Time: 410ms

The plain JDBC call for this query takes around 70-90ms but with micronaut data along with hikari pool the execution time is much greater than the jdbc execution time. We are using Microsoft SQL server database. The related dependencies used in the project are -

<parent>
    <groupId>io.micronaut</groupId>
    <artifactId>micronaut-parent</artifactId>
    <version>3.5.2</version>
  </parent>

  <properties>
    <packaging>jar</packaging>
    <jdk.version>17</jdk.version>
    <release.version>17</release.version>
    <micronaut.version>3.5.2</micronaut.version>
    <micronaut.data.version>3.4.2</micronaut.data.version>
    <micronaut.runtime>netty</micronaut.runtime>
    <exec.mainClass>com.data.Application</exec.mainClass>
  </properties>

<dependency>
      <groupId>io.micronaut.data</groupId>
      <artifactId>micronaut-data-hibernate-jpa</artifactId>
      <scope>compile</scope>
    </dependency>
    <dependency>
      <groupId>io.micronaut.sql</groupId>
      <artifactId>micronaut-jdbc-hikari</artifactId>
      <scope>compile</scope>
    </dependency>
    <dependency>
      <groupId>com.microsoft.sqlserver</groupId>
      <artifactId>mssql-jdbc</artifactId>
      <scope>runtime</scope>
    </dependency>
Enter fullscreen mode Exit fullscreen mode

Below is my datasource configuration in application.yml -

datasources:
  default:
    url: jdbc:sqlserver://xxxxxxxxxxx;databaseName=xxxxxxx;
    username: xxxxxx
    password: xxxxxx
    driverClassName: com.microsoft.sqlserver.jdbc.SQLServerDriver
    dialect: SQL_SERVER

jpa:
  default:
    properties:
      hibernate:
        hbm2ddl:
          auto: none
        generate_statistics: true
        format_sql: true
        show_sql: true

Enter fullscreen mode Exit fullscreen mode

Please suggest what optimization can be done or if any additional configuration can be added to achieve better performance and execution time with micronaut data which is currently much greater than the normal jdbc execution time.

Discussion (0)