1

Oracle JDBC default fetch size is at 10, and I see a performance increase in using fetch size 100 for particular queries with large number of results.

I'm wondering if setting fetch size to 100 across the board on the connector would impact the performance of the queries with small result sets (i.e. 1 or few rows) negatively.

Improve this question
1
  • 3
    It is probably fine but without knowing anything about your queries or setup, I think the best answer is 'Try it. See what happens'.
    – bradimus
    Commented Aug 19, 2016 at 17:08

1 Answer 1

Reset to default
2

Yes, you are correct that the default fetchSize is set to 10. Before tuning this parameter I could recommend you to profile for some time. After profiling, you could set the global settings. For the most cases, developers tend to set it to 100.

I think that you could set global fetch size for the whole project and then, if needed, you could overwrite it for some individual queries where it should be greater/lesser.

Improve this answer
4
  • 1
    Thank you for the comment. Our team is debating weather we should have base at 10 or at 100 and fine tune later in each case.
    – Tim Zhukov-Khovanskiy
    Commented Aug 19, 2016 at 17:48
  • 1
    This is the most reasonable solution. Try profiling and this will help you to decide.
    – Yuri
    Commented Aug 19, 2016 at 17:53
  • it seems that having fetch size > result set doesn't decrease performance.
    – Tim Zhukov-Khovanskiy
    Commented Aug 19, 2016 at 18:30
  • Large fetch size is okay and could give you a better performance but, please, don't forget that it requires more memory for buffers. That's why I'm recommending you to profile :)
    – Yuri
    Commented Aug 19, 2016 at 19:03

Not the answer you're looking for? Browse other questions tagged or ask your own question.