Rows Returned with JDBC

There are two ways to influence the number of rows returned when getting data from a database using JDBC. One is the setFetchSize method, and the other is the setMaxRows method. Both of these methods appear as part of the statement and resultset classes. If set at the statement level it effects all resultsets that result from execution of the statement.

There are three main reasons to want to change these from the driver default setting:

1. To conserve memory in the Java application by reducing the memory required to store data returned from the database.
   
2. Reduction of wait time for obtaining the resultset when retrieving large amounts of data.
   
3. If the application is experiencing performance problems because the jdbc driver is getting a small amount of data with each fetch.

One obvious situation where it makes sense to change this value is an application that displays data on the web with X rows per page. If X is 25, it would make sense to set the fetchsize to 25 with a call similar to stmnt.setFetchSize(25);. Of course it is better to use a variable, instead of hard coding the value.

According to Oracle their JDBC driver default fetchsize is 10. That may be an appropriate setting on modern systems if the rows contain large amounts of data, for example in BLOBs. If the rows are small (name and address) a fetchsize of several hundred or several thousand might be more appropriate.

It is also important when using these calls to do some performance testing to make sure the performance is being improved, not made worse. For reference see links to pages at Sun and Oracle. Those pages offer definitions of both the setFetchSize and setMaxRows methods. For more information the following are worth reading: Stackoverflow.com forum discussion, a page on JDBC performance suggestions, and Chapter 16 on JDBC Tuning (PDF).