May 11, 2009

Consuming big resultsets with JDBC

We ran into this recently at work and the answer wasn't immediately apparent via Google, so here's a summary.

If you execute a query that returns a large number of results (where 'large number' is dependent on the amount of memory you've allocated), you'll probably run into an OutOfMemoryError. The fix for this is likely different depending on the database you use. Our product is deployed on both MS SQL Server and PostgreSQL, so here's the solution for those.

MS SQL Server

Configuration fix. The JDBC connection property you're interested in is 'responseBuffering'. By default this is set to 'full', which means "the entire result set is read from the server when a statement is executed." However, you can set it to 'adaptive' so that "the minimum possible data is buffered when necessary. As far as I can tell you cannot configure what 'minimum possible' means.

Modify JDBC url to include the property:

jdbc:sqlserver://localhost:1433;databaseName=test;responseBuffering=adaptive

(See "Setting the Connection Properties")

PostgreSQL

Code fix. You need to use the 'fetchSize' property of the statement being executed to tell the JDBC driver to tell the server to use cursors. There are a few conditions where this cannot be used (autocommit mode, backward ResultSet scroll mode), but they didn't apply to us.

You should experiment in your app with the size to find a good balance between minimizing memory use and trips to the server to get more data.

Set the fetch size:

Statement st = connection.createStatement();
st.setFetchSize(2500);
ResultSet rs = st.executeQuery( "SELECT * FROM foo" );
...

(See "Getting results based on a cursor")

Next: JMX for configuing Spring?
Previous: Changing your approach