*
The moose likes JDBC and the fly likes Ideal FetchSize for selecting over 120 million rows Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Ideal FetchSize for selecting over 120 million rows " Watch "Ideal FetchSize for selecting over 120 million rows " New topic
Author

Ideal FetchSize for selecting over 120 million rows

Serkan Demir
Ranch Hand

Joined: Feb 03, 2005
Posts: 61
Hi guys,
Could you make an estimate for an ideal fetch size for a huge resultset?

DB: Oracle9
DB and application are on the same network.

thanks lot,

Serkan

http://www.serkandemir.com
http://serkandemir.blogspot.com


A guy from Turkey<br /><a href="http://blogspot.serkandemir.com" target="_blank" rel="nofollow">http://blogspot.serkandemir.com</a>
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1121

Serkan,
I didn't test it with millions of rows, only thousands, but I found that 100 was the optimal fetch-size when the query returns more than 100 rows.

If the query returns less than 100 rows, then setting the fetch size to exactly the number of rows returned gives the best performance (if I remember my test resukts correctly -- it's been a while).

Good Luck,
Avi.
Serkan Demir
Ranch Hand

Joined: Feb 03, 2005
Posts: 61
I have an available test-bed with a table more than 100 millions of rows. So i have made some tests with it. I am sharing the results with you.
1) The network in which the application and database located is important for this kind of fetching optimization.
2) Our application and database are on the same network and it is fairly good and uniform.
3) I have estimated that 10000 is a good fetch-size for this kind of table after the tests. If the fetching size increases more than this number, i realized that the application is effected with Full GC and it longs very much. On the other hand, when i decreases this number, the performance slightly decreases.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Ideal FetchSize for selecting over 120 million rows
 
Similar Threads
html:hidden within logic:iterate
java database connectivity
Do u think SUN does not like me :eek: ?
CD File Structure
Which comes first - Object Modelling or Data Modelling