The network traffic between the application and the database server is one of the key factors of your application performance. If we can reduce the traffic, it will help us improve the performance of the application. The fetch size is the number of rows retrieved from the database at one time. It depends on the JDBC driver. The default fetch size of most of the JDBC drivers is 10. In normal JDBC programming, if you want to retrieve 1,000 rows, then you will need 100 network round-trips between the application and database server to retrieve all of the rows. It will increase the network traffic, and also impact performance. But if we set the fetch size to 100, then the number of network round-trips will be 10. This will greatly improve the performance of your application.

Many frameworks, such as Spring or Hibernate, give you very convenient APIs to do this. If we do not set the fetch size, then it will take the default value and provide poor performance.

The following sets the FetchSize, with standard JDBC calls:

PreparedStatement stmt = null;
ResultSet rs = null;

try 
{
  stmt = conn. prepareStatement("SELECT a, b, c FROM TABLE");
  stmt.setFetchSize(200);

  rs = stmt.executeQuery();
  while (rs.next()) {
    ...
  }
}

In the previous code, we can set the fetch size on each Statement or PreparedStatement, or even on ResultSet. By default, ResultSet uses the fetch size of Statement; Statement and PreparedStatement use the fetch size of a specific JDBC driver.

We can also set the FetchSize in the Spring JdbcTemplate:

JdbcTemplate jdbc = new JdbcTemplate(dataSource);
jdbc.setFetchSize(200);

The following points should be considered when setting the fetch size:

• Make sure that your JDBC driver supports configuring the fetch size
• The fetch size should not be hardcoded; keep it configurable because it depends on JVM heap memory size, which varies with different environments
• If the fetch size is large, the application might encounter an out of memory issue