This week's book giveaway is in the Mac OS forum.
We're giving away four copies of a choice of "Take Control of Upgrading to Yosemite" or "Take Control of Automating Your Mac" and have Joe Kissell on-line!
See this thread for details.
The moose likes JDBC and the fly likes setFetch size Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » JDBC
Bookmark "setFetch size" Watch "setFetch size" New topic
Author

setFetch size

Abhin Balur
Greenhorn

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
Marshal

Joined: May 26, 2003
Posts: 30586
    
154

Abhin,
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.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Abhin Balur
Greenhorn

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
Bartender

Joined: Oct 14, 2005
Posts: 18570
    
    8

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.
 
GeeCON Prague 2014
 
subject: setFetch size