Is there a way to intelligently set fetch size on JdbcTemplate?

Question

I have query that return reports by a date range. If the date range is large, 50k rows may be returned. If the date range is very small, 10 records could be returned. I've found that setting the fetch size to 1000, when 50k rows are returned greatly speeds up the execution time. But setting it to 1000 when 10 rows are returned slows it down and uses up excessive memory. This is just one example, I have many queries that return few or many rows based on various conditions (type of job running, etc).

Ideally, it would be nice if this could be auto-set after the query is executed (but before the rows are returned).

Is there a better way to do this?

I'm using org.springframework.jdbc.core.support.JdbcDaoSupport.SimpleJdbcDaoSupport getJdbcTemplate().setFetchSize(1000);

Answer 1

This is the default fetch size. There is no best value obviously since as you've described it's a trade-off between memory used and database round-trip (a default value of 100 seems to work well enough for me).

Anyway, you can set the fetch size individually for each result set with ResultSet.setFetchSize().