• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Devaka Cooray
  • Liutauras Vilda
  • Jeanne Boyarsky
  • Bear Bibeault
Sheriffs:
  • Paul Clapham
  • Knute Snortum
  • Rob Spoor
Saloon Keepers:
  • Tim Moores
  • Ron McLeod
  • Piet Souris
  • Stephan van Hulst
  • Carey Brown
Bartenders:
  • Tim Holloway
  • Frits Walraven
  • Ganesh Patekar

setFetch size

 
Greenhorn
Posts: 21
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
author & internet detective
Posts: 39399
763
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Abhin Balur
Greenhorn
Posts: 21
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?
 
Sheriff
Posts: 24594
55
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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.
 
Don't get me started about those stupid light bulbs.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!