Re: scrollable result sets performance problems

On Wed, 11 Apr 2007, Radim Kolar SF.NET wrote:

> I have performance problems with scrollable result sets, it looks like
> all rows are fetched despite ResultSet.setFetchSize(). its major
> problem with sets about 11m rows in size and query execution takes a long
> time, so limit/offset can not be used..

As noted in the documentation [1] the driver won't respect the fetch size
unless the ResultSet is TYPE_FORWARD_ONLY so it doesn't work for
scrollable results.

The only real workaround would be to create a scrollable cursor yourself
and issue the appropriate move and fetch commands to make it do what you
want.

Someone started to implement the functionality in the driver, but they
never finished it. [2]

Kris Jurka

[1] http://jdbc.postgresql.org/documentation/82/query.html#query-with-cursor
[2] http://archives.postgresql.org/pgsql-jdbc/2004-05/msg00164.php