Skip site navigation (1) Skip section navigation (2)

Re: Why Is pgSQL's JDBC Slow?

From: <LSanchez(at)ameritrade(dot)com>
To: <oliver(at)opencloud(dot)com>
Cc: <pgsql-jdbc(at)postgresql(dot)org>
Subject: Re: Why Is pgSQL's JDBC Slow?
Date: 2004-07-23 17:52:23
Message-ID: 2898C2526ECBEB448D434BA1F302790902E93D70@scsdcmail1.ameritrade.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
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

pgsql-jdbc by date

Next:From: Bill SchneiderDate: 2004-07-23 18:20:07
Subject: JDBC memory usage
Previous:From: John BrookesDate: 2004-07-23 16:48:51
Subject: Re: Jsp And Postgresql connectivity

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group