Implementand Cache in Quarkus to call a Stored Procedure in Oracle DataBase

[cesarjv]

Good afternoon, I am currently implementing cache in a quarkus microservice to call a STORED PROCEDURE that is located in an ORACLE DB, my question here is that although I have seen improvement in the results of the queries, I see that a query can be executed in a time of 89 ms, while others can be consulted at a time of 11 ms, so I would like to know if it is normal behavior or it should be as low as possible:

Configure the CacheResult and CacheKey (I have 2 search fields in my input SP that must always be both, is it okay to use only one cache? In this case use documentNumber as CacheKey) in my classes public class FindPrepaidAccountByDocumentRepository, which is the implementation of an interface

package org.tmve.account.repository.impl;
import io.quarkus.cache.CacheKey;
import io.quarkus.cache.CacheResult;
import lombok.extern.slf4j.Slf4j;
import org.tmve.account.beans.PrepaidAccount;
import org.tmve.account.repository.IFindPrepaidAccountByDocumentRepository;
import org.tmve.account.utilities.DocIdentityMap;

import javax.inject.Inject;
import javax.inject.Singleton;
import javax.persistence.EntityManager;
import javax.persistence.StoredProcedureQuery;
import java.util.List;

@Singleton
@Slf4j
public class FindPrepaidAccountByDocumentRepository implements IFindPrepaidAccountByDocumentRepository {

    @Inject
    EntityManager entityManager;


    @Override
    @CacheResult(cacheName = "prepaid-account-cache")
    public List<PrepaidAccount> getPrepaidAccount(String documentType,@CacheKey String documentNumber) {

        StoredProcedureQuery storedProcedureQuery=entityManager
                .createNamedStoredProcedureQuery("BuscarCuentasxDocIden");
        storedProcedureQuery.setParameter(1,documentNumber);
        storedProcedureQuery.setParameter(2, DocIdentityMap.map.get(documentType));

        storedProcedureQuery.execute();

        Integer result= (Integer) storedProcedureQuery.getOutputParameterValue(4);
        log.info("Variable RESULT SP: "+result.toString());

        return (List<PrepaidAccount>) storedProcedureQuery.getResultList();
    }
}

I set the expiration time for my cache in my properties to 60 seg:

**quarkus.cache.caffeine."prepaid-account-cache".expire-after-write=60**
quarkus.datasource.db-kind=oracle
quarkus.datasource.jdbc.driver=io.opentelemetry.instrumentation.jdbc.OpenTelemetryDriver
quarkus.datasource.jdbc.url=${URL_CONNECTION_BD:jdbc:otel:oracle:thin:@(description=(address_list=(address =(host=xxxxxxxx)(protocol=tcp)(port=1522))(address=(host=172.28.2.134)(protocol=tcp)(port=1522))(load_balance=yes)(failover=yes))(connect_data=(service_name=IVR)))}
quarkus.datasource.username=${USERNAME_CONNECTION_BD:xxxxxxx}
quarkus.datasource.password=${PASSWORD_CONNECTION_BD:xxxxxxxxx}
quarkus.datasource.jdbc.acquisition-timeout=${JDBC_TIMEOUT:30000}
quarkus.http.port=${PORT:8080}
quarkus.http.ssl-port=${PORT-SSl:8081}
quarkus.hibernate-orm.dialect=org.hibernate.dialect.Oracle12cDialect

quarkus.http.read-timeout=${SERVICE_READ_TIMEOUT:30000}
quarkus.http.connect-timeout=${SERVICE_CONNECT_TIMEOUT:30000}
quarkus.http.timeout=${SERVICE_TIMEOUT:30000}

quarkus.hibernate-orm.log.sql=false
quarkus.hibernate-orm.jdbc.timezone=${TIME_ZONE_JDBC:GMT-4}
quarkus.hibernate-orm.validate-in-dev-mode = false

quarkus.log.level=INFO
quarkus.log.category."org.hibernate".level=INFO
quarkus.log.file.path=/tmp/trace.log
quarkus.log.console.format=%d{dd-MM-yyyy HH:mm:ss} %-5p [%c{2.}] (%t) %s%e%n
quarkus.log.handler.console."STRUCTURED_LOGGING".format=%e%n
quarkus.log.handler.file."STRUCTURED_LOGGING_FILE".enable=true
quarkus.log.handler.file."STRUCTURED_LOGGING_FILE".format=%e%n
# Configure the category and link the two named handlers to it
quarkus.log.category."io.quarkus.category".level=INFO
quarkus.log.category."io.quarkus.category".handlers=STRUCTURED_LOGGING,STRUCTURED_LOGGING_FILE

quarkus.application.name=FindPrepaidAccountByDocument
quarkus.opentelemetry.enabled=true
quarkus.opentelemetry.tracer.exporter.otlp.endpoint=${URL_JAEGER:http://172.28.2.107:4317}

# Parametros para el certificado SSL
quarkus.ssl.native=true
quarkus.http.ssl.certificate.key-store-file=${UBICATION_CERTIFICATE_SSL:srvdevrma1.jks}
quarkus.http.ssl.certificate.key-store-file-type=${TYPE_CERTIFICATE_SSL:JKS}
quarkus.http.ssl.certificate.key-store-password=${PASSWORD_CERTIFICATE_SSLxxxxx}
quarkus.http.ssl.certificate.key-store-key-alias=${ALIAS_CERTIFICATE_SSL:xxxxxxxx}
quarkus.native.add-all-charsets=true
quarkus.http.insecure-requests=disabled
quarkus.http.cors=true
quarkus.jackson.timezone=${TIME_ZONE:GMT-4}

quarkus.swagger-ui.path=/api/FindPrepaidAccountByDocumentBS/swagger-ui/
quarkus.smallrye-openapi.path=/api/FindPrepaidAccountByDocumentBS/swagger
quarkus.swagger-ui.always-include=true

quarkus.smallrye-openapi.info-title=FindPrepaidAccountByDocument
quarkus.smallrye-openapi.info-version=1.0.1
quarkus.smallrye-openapi.info-description=Microservicio que consulta las cuentas prepago asociada a un numero de identidad
quarkus.smallrye-openapi.info-contact-email=gsgci.requerimientos@telefonica.com
quarkus.smallrye-openapi.info-contact-name=Middleware TI

This is how my queries go:

Test number 1 with a response time of 68 ms

Test number 1 with a response time of 8 ms

Is this normal behavior or should it improve?

[quarkus-bot[bot]]

/cc @gwenneg (cache)