pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPerformance incorporate with JReport
3+ messages / 3 participants
[nested] [flat]
* Performance incorporate with JReport
@ 2006-05-17 06:13 kah_hang_ang@toray.com.my
0 siblings, 1 reply; 3+ messages in thread
From: kah_hang_ang@toray.com.my @ 2006-05-17 06:13 UTC (permalink / raw)
To: pgsql-performance
Hi,
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.
I try many times and the result is still the same.
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.
May I know what is the reason of this?
Is it because of the compatibility between JDBC driver with JReport?
Thanks!
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [PERFORM] Performance incorporate with JReport
@ 2006-05-17 19:00 Jim C. Nasby <jnasby@pervasive.com>
parent: kah_hang_ang@toray.com.my
0 siblings, 1 reply; 3+ messages in thread
From: Jim C. Nasby @ 2006-05-17 19:00 UTC (permalink / raw)
To: kah_hang_ang@toray.com.my; +Cc: pgsql-jdbc@postgresql.org
Moving to -jdbc.
On Wed, May 17, 2006 at 02:13:46PM +0800, kah_hang_ang@toray.com.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.
>
> I try many times and the result is still the same.
>
> 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.
>
> May I know what is the reason of this?
> Is it because of the compatibility between JDBC driver with JReport?
This certainly sounds like a likely JDBC issue, so I'm moving this email
to pgsql-jdbc, since someone there is more likely to be able to help
you.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [PERFORM] Performance incorporate with JReport
@ 2006-05-17 19:40 Kris Jurka <books@ejurka.com>
parent: Jim C. Nasby <jnasby@pervasive.com>
0 siblings, 0 replies; 3+ messages in thread
From: Kris Jurka @ 2006-05-17 19:40 UTC (permalink / raw)
To: Jim C. Nasby <jnasby@pervasive.com>; +Cc: kah_hang_ang@toray.com.my; pgsql-jdbc@postgresql.org
> On Wed, May 17, 2006 at 02:13:46PM +0800, kah_hang_ang@toray.com.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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2006-05-17 19:40 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-05-17 06:13 Performance incorporate with JReport kah_hang_ang@toray.com.my
2006-05-17 19:00 ` Jim C. Nasby <jnasby@pervasive.com>
2006-05-17 19:40 ` Kris Jurka <books@ejurka.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox