Reducing Client-side Resource Requirements

The Advanced Server JDBC driver retrieves the results of a SQL query as a ResultSet object. If a query returns a large number of rows, using a batched ResultSet will:

  • Reduce the amount of time it takes to retrieve the first row.
  • Save time by retrieving only the rows that you need.
  • Reduce the memory requirement of the client.

When you reduce the fetch size of a ResultSet object, the driver doesn’t copy the entire ResultSet across the network (from the server to the client). Instead, the driver requests a small number of rows at a time; as the client application moves through the result set, the driver fetches the next batch of rows from the server.

Batched result sets cannot be used in all situations. Not adhering to the following restrictions will make the driver silently fall back to fetching the whole ResultSet at once:

  • The client application must disable autocommit.
  • The Statement object must be created with a ResultSet type of TYPE_FORWARD_ONLY type (which is the default). TYPE_FORWARD_ONLY result sets can only step forward through the ResultSet.
  • The query must consist of a single SQL statement.

Modifying the Batch Size of a Statement Object

Limiting the batch size of a ResultSet object can speed the retrieval of data and reduce the resources needed by a client-side application. Listing 1.5 creates a Statement object with a batch size limited to five rows.

Listing 1.5

// Make sure autocommit is off
conn.setAutoCommit(false);

Statement stmt = conn.createStatement();
// Set the Batch Size.
stmt.setFetchSize(5);

ResultSet rs = stmt.executeQuery("SELECT * FROM emp");
while (rs.next())
  System.out.println("a row was returned.");

rs.close();
stmt.close();

The call to conn.setAutoCommit(false) ensures that the server won’t close the ResultSet before you have a chance to retrieve the first row. After preparing the Connection, you can construct a Statement object:

Statement stmt = db.createStatement();

The following code sets the batch size to five (rows) before executing the query:

stmt.setFetchSize(5);

ResultSet rs = stmt.executeQuery("SELECT * FROM emp");

For each row in the ResultSet object, the call to println() prints a row was returned.

System.out.println("a row was returned.");

Remember, while the ResultSet contains all of the rows in the table, they are only fetched from the server five rows at a time. From the client’s point of view, the only difference between a batched result set and an unbatched result set is that a batched result may return the first row in less time.

Next, we will look at another feature (the PreparedStatement) that you can use to increase the performance of certain JDBC applications.