• 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 Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Ideal FetchSize for selecting over 120 million rows

 
Ranch Hand
Posts: 61
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Ranch Hand
Posts: 1143
1
Eclipse IDE Oracle Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
reply
    Bookmark Topic Watch Topic
  • New Topic