I have a project based on oracle-r2dbc and spring-boot-starter-data-r2dbc. I would like to execute a very simple select :
SELECT * FROM product WHERE number= 'abc222';
The column ‘number’ is an indexed column of type text.
When I run this statement in Oracle Sql Developer, the response arrives in avg time: 0.5 seconds returning 2,728 rows.
I have implemented simple repository:
public interface ProductRepository
extends ReactiveCrudRepository<Product, String> {
@Query("SELECT * FROM product WHERE number = :number ")
Flux<Product> findByNumber(String number);
}
I am calling this repository from a service with this code:
return repository
.findByNumber(number)
.doOnSubscribe(subscription -> log.info("started, db call"))
.doAfterTerminate(()-> log.info("finished db call")) ;
The log shows that the call took much more than I would expect:
2024-09-11T13:37:33.208+02:00 MyController : started, db call
2024-09-11T13:37:36.813+02:00 MyController : finished db call
As you see the time to process the db call takes more than 3 seconds. If I make this call many times, it is still more than 3 seconds, so we can assume its also an average time.
I would like to understand why there is such a big difference. How can I profile the whole call to see which executions consume additional time? maybe some internal processes, mapping etc. Are there any logs which I could switch on to understand what is happening under the hood?
EDIT
I have switched my stack to JDBC instead of R2DBC and the response times are much smaller. current stack is:
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>com.oracle.database.jdbc</groupId>
<artifactId>ojdbc10</artifactId>
<version>19.24.0.0</version>
</dependency>
repository :
@Query(value = "SELECT * FROM product WHERE number = :number ", nativeQuery = true)
List<Product> find(String number );
The average time for this request is 0.8 seconds.
Still not sure why it took so long with the r2dbc approach.
0