Re: ResultSet performance question

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@postgresql.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