File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes setFetch size Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of Java Interview Guide this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "setFetch size" Watch "setFetch size" New topic

setFetch size

Abhin Balur

Joined: Feb 18, 2008
Posts: 21
Wanted to understand how setFetchSize works. I understand the use of setFetchSize; which basically sets the number of records to fetch from a select query at once so as to minimize the round trips to the database. But in cases where the database has huge number of records (say millions) and with the fetchsize of 10(default); how is the user/application/developer supposed to make use of setFetchsize. Is the user expected to quickly utitilize the result set after the first round of '10' records? any example of the use of setfetchsize where large data is queried will help.
Jeanne Boyarsky
author & internet detective

Joined: May 26, 2003
Posts: 33132

It depends on what you are doing with the data. If you are using all of it at once (say for a download), a larger fetch size can be helpful. But not too large. If you are just displaying the first 20 users to the user and waiting for more, it is a different scenario.

The best thing to do is try a few numbers and see what works in your environment.

[OCA 8 book] [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, TOGAF part 1 and part 2
Abhin Balur

Joined: Feb 18, 2008
Posts: 21
Ok. Lets say there are 2 million users; but i want just 20,000 of them. And i choose to go with the default fetch size of 10. How does my code look?.. Do i have to keep waiting over the resultset to check if the slot of 10 is filled? I mean what happens when i am iterating over the resultset in this case and the next batch of 10 is not yet retrieved from the databse?
Paul Clapham

Joined: Oct 14, 2005
Posts: 19973

Abhin Balur wrote:Ok. Lets say there are 2 million users; but i want just 20,000 of them.

This is not what you should be using the fetch size for. Remember that the JDBC driver is free to ignore the "hint" which you provide when you call setFetchSize(). If you only want 20,000 of the users then you should really just write a database query which returns only the 20,000 users you want to see.

However, as far as how the fetch size works, it's the responsibility of the JDBC driver to only fetch 10 records at a time from the database, if it feels like doing that. You don't need to write extra code -- apart from calling setFetchSize() -- to make sure that it's doing its work.
I agree. Here's the link:
subject: setFetch size
It's not a secret anymore!