Thread: Postgres 8.2 and setFetchSize
Hi,
I'm trying to use the setFetchSize method to get back results faster. I'm using PostgreSQL 8.2 under XP. I'm using
postgresql-8.2-505.jdbc3.jardriver. I'm connecting to a Postgres on Linux (PostgreSQL 8.2.4 on i686-pc-linux-gnu,
compiledby GCC gcc (GCC) 4.1.2 20061115 (prerelease) (Debian 4.1.1-21)). I think that the setFetchSize do not work.
I'veread a lot about the conditions for that to work (autocommit false) but it's not working.
Here is my sample code:
public static void main(String[] args) throws Exception{
Connection con=null;
try {
Class.forName("org.postgresql.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
String s_server = "jdbc:postgresql://regulus:5432/ignimport" ;
try {
con = DriverManager.getConnection(s_server, "bs", "");
} catch(Exception e)
{
e.printStackTrace();
}
Statement st = con.createStatement();
con.setAutoCommit(false);
st.setFetchSize(Integer.parseInt(args[0]));
System.out.println("FetchSize : " + st.getFetchSize());
String q="SELECT a FROM SValues WHERE Name = 'title'";
long t1 = System.currentTimeMillis();
ResultSet resultSet = st.executeQuery(q);
if (resultSet.next())
{
String s = resultSet.getString(1);
}
long t2 = System.currentTimeMillis();
System.out.println("exec time : " + (t2 - t1));
}
It always take the same time (long time) whatever value I pass to this code for the FetchSize. It is really a problem
becauseI can't use the LIMIT keywords as I don't know the exact number of records I need.
Any advice ?
Thanks
Frédéric Houbie
I have one more information, I tried the same code using my local Postgres database (on my XP, 8.2.4), it is working.
Whatcan I check on the server to see what's wrong ?
In my log, I can see it is using a cursor
2007-09-19 14:10:51.993 LOG: duration: 0.000 ms parse S_1: BEGIN
2007-09-19 14:10:51.993 LOG: duration: 0.000 ms bind S_1: BEGIN
2007-09-19 14:10:51.993 LOG: duration: 0.000 ms execute S_1: BEGIN
2007-09-19 14:10:51.993 LOG: duration: 0.000 ms parse S_2: SELECT * FROM SValues
2007-09-19 14:10:51.993 LOG: duration: 0.000 ms bind S_2/C_3: SELECT * FROM SValues
2007-09-19 14:10:51.993 LOG: duration: 0.000 ms execute S_2/C_3: SELECT * FROM SValues
20
Thanks
Frédéric
-----Message d'origine-----
De : pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-owner@postgresql.org] De la part de Frédéric Houbie
Envoyé : mercredi 19 septembre 2007 13:54
À : pgsql-jdbc@postgresql.org
Objet : [JDBC] Postgres 8.2 and setFetchSize
Hi,
I'm trying to use the setFetchSize method to get back results faster. I'm using PostgreSQL 8.2 under XP. I'm using
postgresql-8.2-505.jdbc3.jardriver. I'm connecting to a Postgres on Linux (PostgreSQL 8.2.4 on i686-pc-linux-gnu,
compiledby GCC gcc (GCC) 4.1.2 20061115 (prerelease) (Debian 4.1.1-21)). I think that the setFetchSize do not work.
I'veread a lot about the conditions for that to work (autocommit false) but it's not working.
Here is my sample code:
public static void main(String[] args) throws Exception{
Connection con=null;
try {
Class.forName("org.postgresql.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
String s_server = "jdbc:postgresql://regulus:5432/ignimport" ;
try {
con = DriverManager.getConnection(s_server, "bs", "");
} catch(Exception e)
{
e.printStackTrace();
}
Statement st = con.createStatement();
con.setAutoCommit(false);
st.setFetchSize(Integer.parseInt(args[0]));
System.out.println("FetchSize : " + st.getFetchSize());
String q="SELECT a FROM SValues WHERE Name = 'title'";
long t1 = System.currentTimeMillis();
ResultSet resultSet = st.executeQuery(q);
if (resultSet.next())
{
String s = resultSet.getString(1);
}
long t2 = System.currentTimeMillis();
System.out.println("exec time : " + (t2 - t1));
}
It always take the same time (long time) whatever value I pass to this code for the FetchSize. It is really a problem
becauseI can't use the LIMIT keywords as I don't know the exact number of records I need.
Any advice ?
Thanks
Frédéric Houbie
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
Frédéric Houbie wrote:
> st.setFetchSize(Integer.parseInt(args[0]));
> System.out.println("FetchSize : " + st.getFetchSize());
> String q="SELECT a FROM SValues WHERE Name = 'title'";
> It always take the same time (long time) whatever value I pass to this code for the FetchSize.
Fetch size does not directly affect query speed, it affects the number
of results the driver retrieves at once. If you've got a query that is
slow and takes a long time to return any results at all, changing the
fetchsize isn't going to help.
Since it sounds like you are getting quite different results on two
different server installations I would guess that the two databases are
not actually identical. Perhaps one is using an index but the other
isn't? Try the queries via psql, use EXPLAIN, etc.
-O
Frédéric Houbie wrote: > I have one more information, I tried the same code using my local Postgres database (on my XP, 8.2.4), it is working. Whatcan I check on the server to see what's wrong ? > 2007-09-19 14:10:51.993 LOG: duration: 0.000 ms execute S_2/C_3: SELECT * FROM SValues That's a different query to what you said before: > String q="SELECT a FROM SValues WHERE Name = 'title'"; so you're not really running the same code, it seems. -O
I know, two differents tests on two databases, I know also that setFetchSize limit the number of rows that are returned withoutwaiting the full result list. That's what I want. The fact is that the behaviour is different from my pc (windowsXP, pg 8.2.4). I don't know where it could come from. Frédéric -----Message d'origine----- De : Oliver Jowett [mailto:oliver@opencloud.com] Envoyé : mercredi 19 septembre 2007 15:26 À : Frédéric Houbie Cc : pgsql-jdbc@postgresql.org Objet : Re: [JDBC] Postgres 8.2 and setFetchSize Frédéric Houbie wrote: > I have one more information, I tried the same code using my local Postgres database (on my XP, 8.2.4), it is working. Whatcan I check on the server to see what's wrong ? > 2007-09-19 14:10:51.993 LOG: duration: 0.000 ms execute S_2/C_3: SELECT * FROM SValues That's a different query to what you said before: > String q="SELECT a FROM SValues WHERE Name = 'title'"; so you're not really running the same code, it seems. -O
Frédéric Houbie wrote: > I know, two differents tests on two databases, I know also that setFetchSize limit the number of rows that are returnedwithout waiting the full result list. That's what I want. The fact is that the behaviour is different from my pc(windows XP, pg 8.2.4). What does the other database (the one that is slow) log for the same case as this one, then? >> 2007-09-19 14:10:51.993 LOG: duration: 0.000 ms execute S_2/C_3: SELECT * FROM SValues -O
Frédéric Houbie wrote: > I know, two differents tests on two databases, I know also > that setFetchSize limit the number of rows that are returned > without waiting the full result list. That's what I want. The > fact is that the behaviour is different from my pc (windows > XP, pg 8.2.4). > > I don't know where it could come from. What I would do in such a case is to set logLevel=2 in the connection URL and compare the traces. Yours, Laurenz Albe