Intermittent same queries is taking more time.

[domilgarg]

Java version: Java 17
DB Packages:
runtimeOnly 'io.r2dbc:r2dbc-mssql:1.0.2.RELEASE'
implementation 'org.springframework.boot:spring-boot-starter-data-r2dbc'

Implementation DB config Snippet
` ConnectionFactoryOptions baseOptions = ConnectionFactoryOptions.parse(
"r2dbc:sqlserver://" + url);
ConnectionFactoryOptions ob = ConnectionFactoryOptions.builder().from(baseOptions)
.option(USER, user)
.option(PASSWORD, password)
.build();

ConnectionFactory connectionFactory = ConnectionFactories.get(ob);
ConnectionPoolConfiguration configuration = ConnectionPoolConfiguration.builder(
        connectionFactory)
    .maxIdleTime(Duration.ofMinutes(sqlTimeout))
    .initialSize(pool)
    .minIdle(minPoolSize)
    .maxSize(maxPoolSize) // MAX_CONNECTIONS as per RDS instance of 8 GB RAM = 200.
    .build();
return new ConnectionPool(configuration);

`

We are facing 2 intermittent issues.
1- Intermittently queries is taking more time and it is exceeding 1 sec timeout. Simple select query normally returning in 20ms but its P95 is 500ms. We are getting following exception.
TimeoutException: Did not observe any item or terminal signal within 1000ms in 'Mono.map ⇢ at com.thyrocare.laboms.dao.TechsoDbDao.getSampleDetails(TechsoDbDao.java:42)' (and no fallback has been configured)

2- Sometimes it is closing connections. We are getting MssqlconnectionclosedException.
ReactorNettyClient$MssqlConnectionClosedException: Connection closed

Pls suggest what we can do resolve these issues.

[mp911de]

Please share some code to show how you're using the connection. Too many concurrent requests or too much data left unread on the connection may lead to SQL server closing the connection.

[domilgarg]

DB Client Config

@bean
@qualifier("dbConnectionFactory")
public ConnectionFactory dbConnectionFactory() {

ConnectionFactoryOptions baseOptions = ConnectionFactoryOptions.parse(
    "r2dbc:sqlserver://" + url);
ConnectionFactoryOptions ob = ConnectionFactoryOptions.builder().from(baseOptions)
    .option(USER, user)
    .option(PASSWORD, password)
    .build();

ConnectionFactory connectionFactory = ConnectionFactories.get(ob);
ConnectionPoolConfiguration configuration = ConnectionPoolConfiguration.builder(
        connectionFactory)
    .maxIdleTime(Duration.ofMinutes(30))
    .initialSize(10)
    .minIdle(5)
    .maxSize(15) .
    .build();
return new ConnectionPool(configuration);

}

@bean
@qualifier("dbClient")
public DatabaseClient CdBClient(
@qualifier("dbConnectionFactory") ConnectionFactory connectionFactory) {
return DatabaseClient.create(connectionFactory);
}

DB client Implementation

@Autowired
@qualifier("dbClient")
private DatabaseClient DbClient;

public Mono<Optional> getSampleDetails(String bcode) {
Instant start = Instant.now();
String query = "select * from db.dbo.table WITH (NOLOCK) WHERE BCODE = ${bcode} and wo_date>getDate()-7";
String validatedBcode = commonUtil.validateBcode(bcode);
query = query.replace("${bcode}", "'" + validatedBcode + "'");
Mono<Optional> woHeader = DbClient.sql(query)
.map(CMapper.WO_HEADER_MAPPING_FUNCTION).first()
.map(Optional::of)
.timeout(Duration.ofMillis(1000))
.switchIfEmpty(Mono.defer(() -> Mono.just(Optional.empty())))
.onErrorResume(err -> {
log.error("error in DbClient {}", err.getMessage());
return Mono.error(err);
})
.doFinally(v -> {
Instant end = Instant.now();
log.info(" time {} forcode {}", Duration.between(start, end).toMillis(), bcode);
});
return woHeader;
}