Re: JDBC 2.0 Compatibility?

On Mon, Nov 03, 2003 at 12:11:43PM -0800, Renaud Waldura wrote:
> Thanks. I guess part of my question is about how these features are
> implemented.
>
> E.g. I tested scrollable result sets with the 7.3.4 driver, and I got the
> feeling it's implemented by reading all rows into memory first... which
> would negate some of the benefits.

The driver loads the whole resultset into memory at once unless you set a
non-zero fetchsize on the statement, regardless of the resultset type.
If you set a non-zero fetchsize, it will use cursors (DECLARE/FETCH) to
process the results in blocks of 'fetchsize' rows at a time.

However using a non-zero fetchsize is broken with scrollable resultsets
(forward iteration works, but that's it) at the moment. This would take some
work to fix properly; I have a patch pending that "fixes" this by disabling
use of cursors if the resultset will be scrollable, but that obviously
doesn't help performance!

> Same with batch updates (which I didn't test) : they can be emulated by the
> driver, with no performance benefits, or they can be implemented "for
> real". Same with prepared statements.

Batch updates are supported but give you no performance benefit at present
as they just execute each update individually. Improving this is on my radar
but might be a while yet.

Reusing prepared statements is slightly faster than constructing a new
statement each time. There are also methods on org.postgresql.PGstatement to
turn on use of server-side PREPARE/EXECUTE which has a more significant
effect. I have an old patch that automatically enables this after a certain
number of reuses of the statement, so you don't need to do anything
postgresql-specific to enable it, but it needs rework to apply to current
CVS -- and I'm not sure if there's consensus that this is the right thing to
do anyway.

> Maybe what I'm after is a performance document specifically for the PG JDBC
> driver.

There's nothing I know of along these lines.

-O