Skip Headers

# JDBC Fetch Size

Previous

Next

The JDBC fetch size gives the JDBC driver a hint as to the number of rows that should be fetched from the database when more rows are needed.

For large queries that return a large number of objects you can configure the row fetch size used in the query to improve performance by reducing the number database hits required to satisfy the selection criteria.

Most JDBC drivers default to a fetch size of 10, so if you are reading 1000 objects, increasing the fetch size to 256 can significantly reduce the time required to fetch the query's results. The optimal fetch size is not always obvious. Usually, a fetch size of one half or one quarter of the total expected result size is optimal. Note that if you are unsure of the result set size, incorrectly setting a fetch size too large or too small can decrease performance.

Set the query fetch size with `ReadQuery` method `setFetchSize` as Example: JDBC Driver Fetch Size shows. Alternatively, you can use `ReadQuery` method `setMaxRows` to set the limit for the maximum number of rows that any `ResultSet` can contain.

JDBC Driver Fetch Size

    // Create query and set Employee as its reference class
    ReadAllQuery query = new ReadAllQuery(Employee.class);
    ExpressionBuilder builder = query.getExpressionBuilder();
    query.setSelectionCriteria(builder.get("id").greaterThan(100));

    // Set the JDBC fetch size
    query.setFetchSize(50);

    // Configure the query to return results as a ScrollableCursor
    query.useScrollableCursor();

    // Execute the query
    ScrollableCursor cursor = (ScrollableCursor) session.executeQuery(query);

    // Iterate over the results
    while (cursor.hasNext()) {
        System.out.println(cursor.next().toString());
    }
    cursor.close();

In this example, when you execute the query, the JDBC driver retrieves the first 50 rows from the database (or all rows if less than 50 rows satisfy the selection criteria). As you iterate over the first 50 rows, each time you call `cursor.next()`, the JDBC driver returns a row from local memory–it does not need to retrieve the row from the database. When you try to access the fifty first row (assuming there are more than 50 rows that satisfy the selection criteria), the JDBC driver again goes to the database and retrieves another 50 rows. In this way, 100 rows are returned with only two database hits.

If you specify a value of zero (default; means the fetch size is not set), then the hint is ignored and the JDBC driver's default is used.

For more information about configuring JDBC driver properties from within your TopLink application, see "Configuring Properties".