Re: [PERFORM] Performance incorporate with JReport

> On Wed, May 17, 2006 at 02:13:46PM +0800, kah_hang_ang(at)toray(dot)com(dot)my wrote:
>> Currently I'm using postgresql v8.1.3 and the latest jdbc.
>>
>> I try to open a JReports' report and the time taken to completely open the
>> report is 137453ms.
>> Then I open the same report but this time I connect to postgresql v7.2.2
>> but the completion time is even faster than connect to postgresql v8.1.3
>> which took 15516ms to finish.
>>
>> So I think it might be compatibility problem between JReport & Postgresql
>> 8.1.3 so i add in 'protocolVersion=2' in the connection string.
>> Then i open the same report again and this time it just as what i expected,
>> the execution time for the report become 6000ms only,
>> it is 20x times faster than previous test without 'protocolVersion=2'
>> option.
>>

It is very unclear what JReport and/or this specific report is doing, but
there are differences with how PreparedStatements are planned for the V2
and V3 protocol.

For the V2 protocol, the driver manually interpolates the parameters into
the query string and sends the whole sql across upon each execution.
This means the server can know the exact parameters used and generate the
best plan (at the expense of reparsing/replanning on every execution).

For the V3 protocol the driver prepares the query and sends
the parameters over separately. There are two different modes of this
execution depending on whether we expect to re-execute the same statement
multiple times or not (see the prepareThreshold configuration parameter).
If we don't expect to reissue the same query with different parameters it
will be executed on the unnamed statement which will generate a plan using
the passed parameters and should be equivalent to the V2 case. If it is
expected to be reissed it will be executed on a named statement and
prepared with generic parameters that may differ wildly from your actual
parameters resulting in a non-ideal plan.

Also with the V3 protocol on either a named or unnamed statement the JDBC
driver passes type information along with the parameters which may cause
the server to generate a poor plan because it fails to use an index
somehow. Although with 8.0+ cross type indexing usually works.

It's tough to say what's going on here without more detail about what
JReport is actually doing.

Kris Jurka