Reducing client-side resource requirements v42.7.3.1

The EDB Postgres 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:

• Reduces the amount of time it takes to retrieve the first row.
• Saves time by retrieving only the rows that you need.
• Reduces 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.

You can't use batched result sets in all situations. Not adhering to the following restrictions causes the driver to silently fall back to fetching the whole ResultSet at once:

• The client application must disable autocommit.
• You must create the Statement object with a ResultSet type of TYPE_FORWARD_ONLY type (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. The following code creates a Statement object with a batch size limited to five rows:

// 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.");

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 can return the first row in less time.

• On this page
• Modifying the batch size of a statement object