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);

score 3 · Answer 1 · answered Aug 22 '13 at 14:40

3

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().

answered Aug 22 '13 at 14:40

Vincent Malgrat

66,725
9
119
171

Another thing, this query can be executed from the server or from the client. Setting the fetch size appropriately make a huge different when the client is remote (due to the number of trips across the wire). But the client also has limited memory.... – stilltrackin Aug 22 '13 at 14:58

score 2 · Answer 2 · answered Aug 23 '13 at 13:12

I found a solution. I found that fetch size can be modified at any point (even while retrieving rows). My first implementation defaulted the fetch size to 1. When I saw a second row, I changed it to 10. When I saw an 11th row, I changed it to 100, etc. I ran several performance tests using local and remote databases and landed on the following. (Spring JDBC uses row mappers. This is how I implemented org.springframework.jdbc.RowMapper).

public T mapRow(ResultSet rs, int index) {
    T dto = null;

    if (index == 0) {
        setFetchSize(rs, 50);
    } else if (index == 50) {
        setFetchSize(rs, 500);
    } else if (index == 1000) {
        setFetchSize(rs, 1000);
    }

    try {
        dto = mapRowToDto(rs, index);
    } catch (SQLException e) {
        throw new RuntimeException(e.getMessage(), e);
    }

    return dto;
}

Nir Alfasi · Answer 3 · 2013-08-22T14:47:13.300

First, the docs says that SimpleJdbcDaoSupport is deprecated since Spring 3.1 - better use either JdbcDaoSupport or NamedParameterJdbcDaoSupport

Second, in org.springframework.jdbc.core.support Class JdbcDaoSupport there's a method: applyStatementSettings(Statement stmt) which you can use in order to applying statement settings such as fetch size, max rows, and query timeout.

Another option is to LIMIT the number or rows returned in the result set in the query (in MySQL use: LIMIT, for PL\SQL use " and rownum < 50" - for example)

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

3 Answers3