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!
