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.
Joined: Feb 03, 2005
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.
subject: Ideal FetchSize for selecting over 120 million rows