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.
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.
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?
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.