Print this page.  If your browser doesn't allow JavaScript, right-click this page and choose Print from the popup-menu.        
Use Mimer JDBC and Fetch Sizes
Category: JDBC
Introduction

In most database systems, the network is a major performance bottleneck. Each time a client calls the server to perform some work, it waits for the server to return with the requested data. This is something we call a network round-trip.

Each round-trip involves overhead. Not only because it takes time for the message to be transmitted but also because the handling of the network request itself involves work for both the client and the server.

When retrieving large result sets, performance benefits if the correct number of rows from the server is retrieved in each network request. In JDBC, you can control this using the fetch size attribute of the ResultSet object.

About the Fetch Size Attribute

Each ResultSet object has a fetch size attribute. This attribute controls how many rows the driver should retrieve from the server in each server request.

If fetch size is 1, the driver will get one row from the server with each ResultSet.next() method call.

If the fetch size is 20, the driver will get twenty rows from the server on the first ResultSet.next(). All 20 rows will be kept in the driver network buffer. The subsequent 19 ResultSet.next() calls will not talk to the server at all. On the 20th ResultSet.next() the server will be asked for the next 20 rows.

The driver is not required to use the fetch size strictly. The driver should use the fetch size only as a recommendation from the application. However, Mimer JDBC does follow a specified fetch size.

When the ResultSet object is created, it inherits the fetch size from the statement object (Statement, PreparedStatement or CallableStatement) that created the ResultSet. The fetch size attribute for the statement object can be manipulated just like the ResultSet attribute but its sole purpose is to provide a default for ResultSet objects.

If the application does not specify a fetch size at all, Mimer JDBC will use whatever fetch size that fits into a 60Kb buffer. If each row takes up 100 bytes the default fetch size will be 600.

Example

How do I set the fetch size?

There are two ways you can set the fetch size:
  • Set the fetch size on the statement object, thus affecting all ResultSet objects created by the statement object
  • Set the fetch size individually on each ResultSet object created.

For example, the code sample below creates a PreparedStatement and sets the default fetch size to 1000 rows. This setting could be suitable for a non-interactive caller. Sometimes an interactive part of the application wants to present the same information to the user one screen at a time. In this case, a result set is created from the PreparedStatement and the fetch size is set to 20.
class myClass {
PreparedStatement pstmt;

myClass() {
pstmt = con.prepareStatement(“select * from MYTABLE”);
pstmt.setFetchSize(1000);
}

void doInteractiveQuery() {
ResultSet rs = pstmt.executeQuery();
rs.setFetchSize(20);
while (rs.next()) {
...do_something_useful...
}
}
}

What fetch size should I choose?

The best fetch size depends on the requirements of your application.

Basically, we are concerned with response time and overall time.
Small fetch sizes improve response times but the overall execution time deteriorates. Large fetch sizes improve overall execution time but result in longer response times.

Displaying Result Sets for Users

Applications that need to display parts of result sets to end-users want to bring up the first screen as fast as possible. Response time is important and we don’t want to wait for excessive rows to be transferred.
But, in the end, we don’t really know if the user really wants to look at the next screen.

If we were going to display 30 rows at a time on screen, an appropriate fetch size would be around 30 but not less than that. We want to bring all rows to be displayed on screen over the network at the same time, and to return as quickly as possible to display data.

Non-interactive Applications

Non-interactive applications will almost always perform best with large fetch sizes. Response time is not an issue and the applications have no problem waiting for a long time while all rows are transferred knowing that, in the end, it saves time.

Impact of Slow Networks

The effects of the fetch size are most easily seen when running on a slow network, such as a modem connection.

A modem transfers currently (November 2001) at most 5 KB per second, This means a fetch size of 1000 on 100 byte rows involves a response time of at least 20 seconds. If we are interested in the first 30 rows for display on screen we can get away with less than a second if we set the fetch size to 30.

Conclusion

For performance that matches your requirements, set the fetch size!

Last updated: 2002-08-27

 

Powered by Mimer SQL

Powered by Mimer SQL