CODEWITHSUNDEEP
oracle-databasejdbc
Massimiliano
Massimiliano

Reputation: 665

JDBC performance tuning - setFetchSize

I'm trying to develop a Scala microservice for data management for an Oracle database. I'm using JDBC drivers to connect to it.

Reading the answers to the performance questions regarding JDBC driver compared to the .NET one, I've understood that one of the more effective vehicle to tune the JDBC reading performance is to set the Fetch Size through the method ResultSet.setFetchSize.

I've tried connecting to an Oracle database to fetch real data for a real business case, with a fixed number of record returned by the DB, and I've measured an exponential behavior of the elapsed time. In particular, fetching 10,000 rows from the database without setting the fetch size resulting in a ridicolously large amount of fetch time, but specifying a fetch size larger than 1,000 resulting in a little amount of time gained (roughly 100 ms over 1 s).

Here's my questions regarding this topic:

  1. I suppose that incrementing too much the fetch size would consume resources inopportunely for a little gain, so is there an even rough method to estimate the size of the ResultSet before actually fetching it? I've read about the following technique:

    result.last(); result.getRow();

    but this would mean scroll the entire ResultSet, and I was wondering if there's any even rough accurate technique to evaluate the count;

  2. I've estimated that a good fetch size would be 1/10th of the number of record selected, but is there a documented rule to try to automatically estimate the correct fetch size for the largest number of cases?

Upvotes: 2

Views: 7243

Answers (1)

yaoweijq
yaoweijq

Reputation: 293

Please do not set fetch size too large, unless you have network bottleneck between application and database. The larger the fetch size, the more memory consumed.

In my experience, 1024 - 2048 will lead to best performance most of the time. See https://docs.oracle.com/javase/tutorial/jdbc/basics/retrieving.html discussing some details, but the default setting is usually best.

Do not try to get the total numbers of rows in the result set, it is not the best practice.

And finally, I want to point out that based on the hundreds of thousands of time optimize about JVM and jit, the bottleneck seems never happens on fetch size of JDBC after you set it with 1000-2000, but on the SQL performance, applications or resource limit and etc.

Upvotes: 2

Related Questions