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

Re: ResultSet performance question

From: Craig Servin <cservin(at)cromagnon(dot)com>
To: pgsql-jdbc(at)postgresql(dot)org
Cc: JAlexoid <jalexoid(at)gmail(dot)com>, Andres Ledesma <aledes(at)euskalnet(dot)net>, "Albe Laurenz" <laurenz(dot)albe(at)wien(dot)gv(dot)at>
Subject: Re: ResultSet performance question
Date: 2008-12-18 21:18:53
Message-ID: 200812181518.53512.cservin@cromagnon.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
Thank you all for the suggestions and help.

It turned out to be a little bit of a wild goose chase.  We still have not 
found the problem.  But, it is not in the JDBC driver, but in what our 
application does with the result.

We had a performance problem with a report in our production application.  
When I ran that query in psql it ran in under 3 seconds in Java it ran in 30.  
This made me jump to the conclusion that the performance problem was caused 
by the JDBC driver.  This is where things get interesting.  I was running the 
tests from my laptop through our production firewall via SSL connections to 
Postgres.  When I moved the test code to our production environment the 
problem with the test code performance went away and the JDBC ran in just 4 
seconds vs. the 3 in psql.

For some reason SSL via the JDBC driver through our firewall performed much 
worse than psql and disguised the real issue.

Thanks again for all of the suggestions,

Craig


On Thursday 18 December 2008 04:24:32 am JAlexoid wrote:
> A) If you are using PreparedStatement that should involve a conditional
> index when parameters are applied, the index will not be used.
> B) Have you tries to set the fetch size? (Statement.setFetchSize())
>    0 - means all rows will be transferred at once
>    more than 0 - effective fetch will be done.
>
> Craig Servin-2 wrote:
> > I'm having a performance issue with Postgresql, but only when using the
> > JDBC
> > driver.  I'm looking for any suggestions.
> >
> >
> > I have a very simple query that returns about 7000 rows from a temp
> > table. Using psql the query returns in under 3 seconds.  Using the JDBC
> > driver it takes around 30 seconds to run the same query.
> >
> > I have looked at the query plan using both methods of execution and they
> > are
> > identical.  I have played around with setFetchSize() and used
> > con.createStatement(ResultSet.TYPE_FORWARD_ONLY,
> > ResultSet.CONCUR_READ_ONLY);
> > But, since I am trying to get all of the results and it is total runtime
> > I am
> > worried about server side cursors don't seem applicable.
> >
> > I have also created a Java class that does nothing but issue rs.next()
> > calls
> > through the ResultSet to time the execution without any other work being
> > done.
> >
> > I can't believe that I am not doing something wrong as the difference in
> > execution time is extreme.  Is this normal?
> >
> > Any advice would be greatly appreciated.
> >
> > Thanks,
> >
> > Craig
> >
> >
> > --
> > Sent via pgsql-jdbc mailing list (pgsql-jdbc(at)postgresql(dot)org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-jdbc
>
> --
> View this message in context:
> http://www.nabble.com/ResultSet-performance-question-tp21040330p21070092.ht
>ml Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.



-- 
The worst thing about bad code is that sometimes it works!
                               -- Sharon Misgen

In response to

pgsql-jdbc by date

Next:From: Oliver JowettDate: 2008-12-18 23:56:16
Subject: Re: postgresql-8.1-413.jdbc3.jar ignoring setPrepareThreshold();
Previous:From: Jeremiah JahnDate: 2008-12-18 17:22:07
Subject: postgresql-8.1-413.jdbc3.jar ignoring setPrepareThreshold();

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