[FIXED] Database function execution running slow with JdbcTemplate/SimpleJdbcCall in Spring Boot

Issue

Facing performance issue in database function execution with Spring Boot JdbcTemplate (SimpleJdbcCall), repository class method take average 300 to 400 milliseconds to execute, as we are calling this method in loop creating major performance hurdle for us. The default connection pool we are using (DATASOURCE = HikariDataSource (HikariPool-1))

There is no problem with database function as same function used in legacy application(not spring boot application), where database connection written in old way. This DAO/Repository method called in loop almost 4000 times in legacy application it works properly and took average 1 or 2 milliseconds to execute method, almost 300 percent less time.

application.properties entries

spring.datasource.url=jdbc:oracle:thin:@//xxxxxxxxxxxx
spring.datasource.username=XXXX
spring.datasource.passwordl=YYYY
spring.datasource.initial-size=10
spring.datasource.max-active=50

Repository class code snippet

public Double getXXXX(Map<String, String> rs){

SimpleJdbcCall jdbcCall = new SimpleJdbcCAll(jdbcTemplate)
.withCatalogName(“XXXXXXX”)
.withFunctionName(“YYYYYY”);

SqlParameterSource inPrm = Map SqlParameterSource()
        .addValue(“AAA”, rs.get(“A”))
        .addValue(“BBB”, rs.get(“B”));

Number resultVal = jdbcCall.executeFunction(Number.class, inPrm);
Return resultVal == null ? 0.0 : resultVal.doubleValue();
}

We also tried to change dtabase connection pool from HikariPool to apache tomcate jdbc pool (DATASOURCE = org.apache.tomcat.jdbc.pool.DataSource@7c541c15...), and we set application properties related to tomcate database pool but no luck.

spring.datasource.url=jdbc:oracle:thin:@//xxxxxxxxxxxx
spring.datasource.username=XXXX
spring.datasource.tomcate.initial-size=20
spring.datasource.tomcate.max-wait=-1
spring.datasource.tomcate.max-active=120
spring.datasource.tomcate.max-idle=20
spring.datasource.tomcate.min-idle=9

Please help to resolve the issue.

Solution

We able to resolve the performance issue, by creating SimpleJdbcCall object in init method annotated with @PostConstruct. as describe in the mkyong.com (https://mkyong.com/spring-boot/spring-boot-jdbc-stored-procedure-examples/)

private SimpleJdbcCall simpleJdbcCallFunction1;
private SimpleJdbcCall simpleJdbcCallFunction2;

// init SimpleJdbcCall
@PostConstruct
public void init() {

    jdbcTemplate.setResultsMapCaseInsensitive(true);

    simpleJdbcCallFunction1 = new SimpleJdbcCall(jdbcTemplate)
        .withFunctionName("XXXXXXXX");
        
    simpleJdbcCallFunction2 = new SimpleJdbcCall(jdbcTemplate)
        .withFunctionName("YYYYYYYYY");
}

It resolve the performance issue and now database call executed in 1 millisecond, but I have doubt creating multiple SimpleJdbcCall object in @PostConstruct init() method is right solution or it will have any side effect. If anyone know better solution please let us know

Answered By - anuragR
Answer Checked By - Marie Seifert (JavaFixing Admin)