Skip site navigation (1) Skip section navigation (2)

very large result sets and ResultSet.relative() to jump to a desired offset

From: "Matt Magoffin" <postgresql(dot)org(at)msqr(dot)us>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: very large result sets and ResultSet.relative() to jump to a desired offset
Date: 2008-09-24 01:56:18
Message-ID: 50687.192.168.1.106.1222221378.squirrel@msqr.us (view raw or flat)
Thread:
Lists: pgsql-jdbc
When executing a large query, I am to understand that for server-side
cursors to be used (so to not run out of memory by loading the entire
result set into memory at once) I need to specify
ResultSet.TYPE_FORWARD_ONLY and then stmt.setFetchSize(x).

However, I'd like to be able to jump to the Xth result and display only Y
results, i.e. support pages of results and allow jumping to arbitrary
pages and thus arbitrary offsets in the result query. In code I used to do
set ResultSet.TYPE_SCROLL_INSENSITIVE, stmt.setFetchSize(Y), and then
rs.relative(X) where stmt is a Statement and rs is a ResultSet.

This causes the entire result set to get loaded into memory. To use a
FORWARD_ONLY result, I can't use ResultSet.relative() anymore. Is there
any alternative, other than perhaps calling ResultSet.next() X times
before returning the results? For very large results (millions of rows)
this seems like a slow solution, as I assume that all results will
actually get passed through JDBC just to jump to the desired row.

-- m@




Responses

pgsql-jdbc by date

Next:From: Oliver JowettDate: 2008-09-24 06:00:52
Subject: Re: very large result sets and ResultSet.relative() to jump to a desired offset
Previous:From: Kris JurkaDate: 2008-09-23 22:21:12
Subject: Re: stored procedure returning result set.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group