Thread: scrollable result sets performance problems
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.. any known workarounds? I am porting oracle app to pgsql.
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