Thread: Postgres 8.2 and setFetchSize

Postgres 8.2 and setFetchSize

From
Frédéric Houbie
Date:
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


Re: Postgres 8.2 and setFetchSize

From
Frédéric Houbie
Date:
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

Re: Postgres 8.2 and setFetchSize

From
Oliver Jowett
Date:
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

Re: Postgres 8.2 and setFetchSize

From
Oliver Jowett
Date:
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

Re: Postgres 8.2 and setFetchSize

From
Frédéric Houbie
Date:
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

Re: Postgres 8.2 and setFetchSize

From
Oliver Jowett
Date:
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

Re: Postgres 8.2 and setFetchSize

From
"Albe Laurenz"
Date:
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