Обсуждение: ResultSet performance question
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
Craig Servin 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. Did you turn on statement tracing on the server an look at the actual query strings that arrive at the server? Else, how do you know what exactly is executed? Is there a server side cursor involved in one of this cases? This can change the execution plan. > 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. How is the time distributed? Does it take a long time until the first row is fetched, and the rest comes in almost no time? Or are the 30 seconds more evenly distributed? > I can't believe that I am not doing something wrong as the difference in > execution time is extreme. Is this normal? If the execution plans are really identical, that would seem out of the ordinary, even considering Java's comparative slowness. Yours, Laurenz Albe
Hi, Can you do a test, creating a PL/PgSQL function and invoking such function from your Java code ? Andrés.
Hi Craig, So, what I would do is : 1- create first a VIEW, to get the data out of the tables. 2- create a function that opens a CURSOR, based on the VIEW created in the step 1, and returns it 3- In my Java code, via a CallableStatement, get returned data as a ResultSet That way you have some gains in time used parsing the query. Regards, Andrés
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.html Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.
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