Re: Why Is pgSQL's JDBC Slow? - Mailing list 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@opencloud.com]
Sent: Thursday, July 22, 2004 6:38 PM
To: LSanchez@ameritrade.com
Cc: books@ejurka.com; pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] Why Is pgSQL's JDBC Slow?

LSanchez@ameritrade.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