Re: Why Is pgSQL's JDBC Slow?

Thanks so much for your response Oliver! I apologize for copying you
again on this because I just want to make sure that you receive it, if
not posted on the pgsql-jdbc mailing list.

> you are not subscribed to the list with the address you are sending
from

I actually subscribed to the mailing lists using this email address and
I've been getting all of the posts, but just not able to post anything.

> Are you comparing the same times here?

That's true, we're not comparing the same times, but please note the
following results when the same exact code was run against Oracle (using
the same client machine and same exact db server specs):

select * from eventlog where eventdate between '1/14/1998' and
'1/15/1998'
No. of records returned: 1101

pSQL pgSQL JDBC Oracle
8 ms 720 ms 172 ms

Select * from eventlog a, eventcodes b, statuschange c Where a.eventid =
c.eventid and a.code = b.code
No. of records returned: 435

pSQL pgSQL JDBC Oracle
22 ms 2500 ms 754 ms

Also checked GC and found that it's only taking on average 4 ms, so that
doesn't seem to be the problem.

> You can try the latest development drivers from jdbc.postgresql.org --

I'll also try using the latest development drivers. Would you know if
there are any 3rd party ODBC drivers that had a great performance track
record?

Have a great day!

Regards,
:) Lily Anne


-----Original Message-----
From: Oliver Jowett [mailto:oliver(at)opencloud(dot)com]
Sent: Thursday, July 22, 2004 6:38 PM
To: LSanchez(at)ameritrade(dot)com
Cc: books(at)ejurka(dot)com; pgsql-jdbc(at)postgresql(dot)org
Subject: Re: [JDBC] Why Is pgSQL's JDBC Slow?

LSanchez(at)ameritrade(dot)com wrote:

> I'm a member of the pgsql-jdbc mailing list but it seems that my email

> (which I sent multiple times since July 8) just can't get posted onto
> the site, so I thought of going to you directly because I see your
names
> very often in the list.

The list can be unreliable at times, or it may be that your email is
getting delayed for approval (e.g. because you are not subscribed to the

list with the address you are sending from).

> We've been doing our pgsql testing and the biggest problem that we've
> encountered so far is the JDBC slowness, which could be 1000x slower
in
> some cases in comparison to native sql and could be 500x slower
compared
> to Oracle.
>
> Are there any newer drivers around that had fixed this problem?

You can try the latest development drivers from jdbc.postgresql.org --
they may help but without knowing what exactly is causing the slowness I

can't say for sure.

> *Sql
> statement

> psql jdbc*
>
> select * from eventlog where eventdate between '1/14/1998' and
> '1/15/1998' 8 ms 720 ms
>
> Select * from eventlog a, eventcodes b, statuschange c Where a.eventid
=
> c.eventid and a.code = b.code 22 ms 2500 ms

Are you comparing the same times here?

I notice in your JDBC code you are timing the execution time but not the

time to iterate through the resultset. With the way you are setting up
your statements, JDBC will be pulling the entire resultset into memory
immediately on execute, so you're actually measuring both the query
execution time and the time taken to pull the results over to the Java
side. Are you also measuring the time to retrieve the results when using

psql?

Are you "warming up" the JVM at all before taking measurements?
Depending on the JVM you use, it may take some time before the critical
bits of code are JIT-ed.

I'd also suggest checking your heap settings under JDBC, You appear to
be moving a lot of data and it may be that some of the slowness you see
is actually time spent doing GC. Try running with '-verbose:gc' and see
how long collection is taking.

If it *is* heap/GC, you have a couple of options:

1) tune your heap/GC settings (probably means a much bigger heap)
2) make JDBC use a cursor to incrementally fetch the resultset; this
will decrease JDBC's working heap size which may help GC.

2) requires (under the current driver):

- a 7.4 or later server
- setAutoCommit(false)
- TYPE_FORWARD_ONLY, not TYPE_SCROLLABLE_INSENSITIVE
- calling Statement.setFetchSize() with a positive value before
executing the query (this controls the number of rows fetched at once)

Earlier drivers (pre build 302) didn't have the need-a-7.4-server
restriction. If there's demand, I could reimplement the
cursors-via-DECLARE support for 7.3.

-O

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org