Win a copy of Mesos in Action this week in the Cloud/Virtualizaton forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Ideal FetchSize for selecting over 120 million rows

 
Serkan Demir
Ranch Hand
Posts: 61
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Avi Abrami
Ranch Hand
Posts: 1141
1
Java Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 61
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic