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

RE: [INTERFACES] JDBC Query performance

From: Peter Mount <petermount(at)it(dot)maidstone(dot)gov(dot)uk>
To: Postgres mailing lists <postgres(at)weblynk(dot)com>, pgsql-interfaces(at)postgresql(dot)org
Cc: "Peter Mount (Home)" <peter(at)retep(dot)org(dot)uk>
Subject: RE: [INTERFACES] JDBC Query performance
Date: 1999-03-18 09:07:10
Message-ID: A9DCBD548069D211924000C00D001C44191B95@exchange.maidstone.gov.uk (view raw or flat)
Thread:
Lists: pgsql-interfaces
The delay is because the driver currently retrieves the entire result
into a Vector() before returning the ResultSet.

I have plans (but it won't be in there) for the JDBC2 ResultSet class to
have the ability of using a cursor, so the return time is improved - but
this won't be done until after 6.5 is out (lack of time).

For now, if you are expecting a large number of results each time, you
could issue the cursor calls yourself.

Peter

--
Peter T Mount, IT Section
petermount(at)it(dot)maidstone(dot)gov(dot)uk
Anything I write here are my own views, and cannot be taken as the
official words of Maidstone Borough Council

-----Original Message-----
From: Postgres mailing lists [mailto:postgres(at)weblynk(dot)com]
Sent: Thursday, March 18, 1999 8:54 AM
To: pgsql-interfaces(at)postgreSQL(dot)org
Subject: [INTERFACES] JDBC Query performance


Hello,
    I've been using the JDBC driver that came with postgres 6.4.2. It
seems
to work pretty well, but noticed that the query performance seemed very
low.
For example, I have a table with about 1200 rows, 20 columns. I'm not
using
indexes yet. I do a query using psql like "select * from mytable" and
pipe
it to a file. It takes about a second or so to complete and I notice the
CPU
idle time goes to maybe 50%. Pretty fast. Doing the same query using the
JDBC driver takes the CPU idle to 0% for about 90 seconds, then finally
returns. A code fragment...

String sql = "select * from mytable";
 Connection conn = get_con();  // uses a DbConnectionBroker class.
Performs
the same with or without this class.
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);

           more = rs.next();

Trace("Finished Query. Filling Vector." );

            while (more)  {
    count ++;
    vector.addElement((new Long(rs.getLong("ID")).toString()));
    vector.addElement(rs.getString("Name"));
    etc...
    more = rs.next();
}

rs.close();
stmt.close();
...


The executeQuery(sql) statement takes about 90 secs, then I see the
Trace
output, then the vector operations take about 10-15 secs. I suppose I
can
live with the Vector op time, if there is no more effecient way of
getting
query results, (I haven't really looked into it), but 90 secs for
executing
the query seems unreasonable.
Anyone else experience this type of thing?

Thanks in Advance.
Rich.



Responses

pgsql-interfaces by date

Next:From: Jean-Francois RouanetDate: 1999-03-18 14:01:14
Subject: pgaccess for windows
Previous:From: Postgres mailing listsDate: 1999-03-18 08:53:36
Subject: JDBC Query performance

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