Granny's Programming Pearls
"inside of every large program is a small program struggling to get out"
The moose likes JDBC and Relational Databases and the fly likes Significance of  ResultSet.setFetchSize() Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Significance of  ResultSet.setFetchSize()" Watch "Significance of  ResultSet.setFetchSize()" New topic

Significance of ResultSet.setFetchSize()

Chetan Parekh
Ranch Hand

Joined: Sep 16, 2004
Posts: 3640
We are using Oracle 8i as DB Server, WebLogic 6.1 as App Server and IIS 5.0 as Web Server on the different physical m/c. We are using OCI8 Driver.

We have a utility that will act as a cache manager. We have to provide a query, it will hold the result in ArrayList.

We are measuring time taken by the DB Server to execute query and how much by App Server to fetch rows using while(

Now in non-peak hours
Query Execution Time : 1000
Row Fetching Time : 100

But during the peak hours
Query Execution Time : 2000
Row Fetching Time : 2000 (sometime 3000).

I understood that during the peak hours DB Server is very busy hence it is taking more time to execute query. And during the peak hours, network traffic is very high + DB Server is already busy hence Row Fetching Time is going very high.

During the peak hours our App Server is 40% ideal always.

I believe that when we do while(, each time App Server make a network trip to DB Server for fetching a single row. So if my query is returning 100 rows, there will be 100 network trip from App Server to DB Server and hence performance is degraded.

Now ResultSet has setFetchSize() method that determine now many records will be fetched on a call by OCI8 driver.

So now I set fetch size as 10...
(1) Will I gain a performance?
(2) Will I have any performance loss by any other means across the application?

Chetan Parekh

My blood is tested +ve for Java.
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1141

According to the Oracle "JDBC User's Guide and Reference", the default fetch size is ten (10). In tests that I performed, I found that a fetch size of one hundred (100) gave optimal performance. But of-course your mileage may vary (YMMV).

Good Luck,
Chetan Parekh
Ranch Hand

Joined: Sep 16, 2004
Posts: 3640
Thx Avi for you reply.

In fact I also done some testing here and I found that 100 can be the ideal.

Presently I have set fetch size 100 at single place, will get the performance report after 2 days, will update you about the same.
I agree. Here's the link:
subject: Significance of ResultSet.setFetchSize()
jQuery in Action, 3rd edition