Hi everyone,
We are using ODP.NET in a .NET WPF application and are looking into optimizing query performance by configuring FetchSize. Some tests have shown that FetchSize can have a very significant impact on execution time, so we want to make sure we are handling this correctly.
We are trying to understand what the recommended best practice is here. Is it generally safe to simply set OracleConfiguration.FetchSize to a high value globally and leave it at that, or are there negative consequences we should be aware of? If so, what would be a reasonable upper limit?
Alternatively, should FetchSize be adjusted dynamically per query? For example, would it make sense to first execute a COUNT query to determine the expected number of rows, and then set the FetchSize accordingly before running the actual query? Or would the overhead of the additional query negate the benefit, especially for complex queries? Is this something that is only worth doing in specific cases, or can it be applied as a general pattern?
What we are looking for is a best practice that can ideally be applied centrally in our application to achieve a noticeable performance improvement across the board, without having to fine-tune every individual query. How do others approach this in practice?
Thanks in advance!