pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedJDBC Query performance
8+ messages / 4 participants
[nested] [flat]
* JDBC Query performance
@ 1999-03-18 08:53 Postgres mailing lists <postgres@weblynk.com>
0 siblings, 0 replies; 8+ messages in thread
From: Postgres mailing lists @ 1999-03-18 08:53 UTC (permalink / raw)
To: pgsql-interfaces
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* RE: [INTERFACES] JDBC Query performance
@ 1999-03-18 09:07 Peter Mount <petermount@it.maidstone.gov.uk>
1999-03-21 14:47 ` RE: [INTERFACES] JDBC Query performance Herouth Maoz <herouth@oumail.openu.ac.il>
0 siblings, 1 reply; 8+ messages in thread
From: Peter Mount @ 1999-03-18 09:07 UTC (permalink / raw)
To: Postgres mailing lists <postgres@weblynk.com>; pgsql-interfaces; +Cc: Peter Mount (Home) <peter@retep.org.uk>
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@it.maidstone.gov.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@weblynk.com]
Sent: Thursday, March 18, 1999 8:54 AM
To: pgsql-interfaces@postgreSQL.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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* RE: [INTERFACES] JDBC Query performance
1999-03-18 09:07 RE: [INTERFACES] JDBC Query performance Peter Mount <petermount@it.maidstone.gov.uk>
@ 1999-03-21 14:47 ` Herouth Maoz <herouth@oumail.openu.ac.il>
1999-03-21 17:44 ` RE: [INTERFACES] JDBC Query performance Peter T Mount <peter@retep.org.uk>
0 siblings, 1 reply; 8+ messages in thread
From: Herouth Maoz @ 1999-03-21 14:47 UTC (permalink / raw)
To: Peter Mount <petermount@it.maidstone.gov.uk>; Postgres mailing lists <postgres@weblynk.com>; pgsql-interfaces
At 11:07 +0200 on 18/03/1999, Peter Mount wrote:
>
> The delay is because the driver currently retrieves the entire result
> into a Vector() before returning the ResultSet.
Couldn't it also be because of Java's usage of network sockets compared to
psql's use of unix sockets?
Herouth
--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma
^ permalink raw reply [nested|flat] 8+ messages in thread
* RE: [INTERFACES] JDBC Query performance
1999-03-18 09:07 RE: [INTERFACES] JDBC Query performance Peter Mount <petermount@it.maidstone.gov.uk>
1999-03-21 14:47 ` RE: [INTERFACES] JDBC Query performance Herouth Maoz <herouth@oumail.openu.ac.il>
@ 1999-03-21 17:44 ` Peter T Mount <peter@retep.org.uk>
0 siblings, 0 replies; 8+ messages in thread
From: Peter T Mount @ 1999-03-21 17:44 UTC (permalink / raw)
To: Herouth Maoz <herouth@oumail.openu.ac.il>; +Cc: Peter Mount <petermount@it.maidstone.gov.uk>; Postgres mailing lists <postgres@weblynk.com>; pgsql-interfaces
On Sun, 21 Mar 1999, Herouth Maoz wrote:
> At 11:07 +0200 on 18/03/1999, Peter Mount wrote:
>
>
> >
> > The delay is because the driver currently retrieves the entire result
> > into a Vector() before returning the ResultSet.
>
> Couldn't it also be because of Java's usage of network sockets compared to
> psql's use of unix sockets?
There shouldn't be that much of a difference, especially on modern
machines when using either unix domain sockets or network sockets.
Peter
--
Peter T Mount peter@retep.org.uk
Main Homepage: http://www.retep.org.uk
PostgreSQL JDBC Faq: http://www.retep.org.uk/postgres
Java PDF Generator: http://www.retep.org.uk/pdf
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [INTERFACES] JDBC Query performance
@ 1999-03-21 19:10 Postgres mailing lists <postgres@weblynk.com>
0 siblings, 0 replies; 8+ messages in thread
From: Postgres mailing lists @ 1999-03-21 19:10 UTC (permalink / raw)
To: pgsql-interfaces
>At 11:07 +0200 on 18/03/1999, Peter Mount wrote:
>
>
>>
>> The delay is because the driver currently retrieves the entire result
>> into a Vector() before returning the ResultSet.
>
>Couldn't it also be because of Java's usage of network sockets compared to
>psql's use of unix sockets?
>
You know, I also noticed that it is the postgres back-end process which is
using all the CPU time during the query. Doesn't sound like Vector
operations to me, which should cause the java process to eat all the CPU
time.
Rich.
^ permalink raw reply [nested|flat] 8+ messages in thread
* RE: [INTERFACES] JDBC Query performance
@ 1999-03-22 09:24 Peter Mount <petermount@it.maidstone.gov.uk>
0 siblings, 0 replies; 8+ messages in thread
From: Peter Mount @ 1999-03-22 09:24 UTC (permalink / raw)
To: Postgres mailing lists <postgres@weblynk.com>; pgsql-interfaces; +Cc: Peter Mount (Home) <peter@retep.org.uk>
Hmmm, this is interesting. Does the cpu time match when the same queries
are run through PSQL?
Peter
--
Peter T Mount, IT Section
petermount@it.maidstone.gov.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@weblynk.com]
Sent: Sunday, March 21, 1999 7:11 PM
To: pgsql-interfaces@postgreSQL.org
Subject: Re: [INTERFACES] JDBC Query performance
>At 11:07 +0200 on 18/03/1999, Peter Mount wrote:
>
>
>>
>> The delay is because the driver currently retrieves the entire result
>> into a Vector() before returning the ResultSet.
>
>Couldn't it also be because of Java's usage of network sockets compared
to
>psql's use of unix sockets?
>
You know, I also noticed that it is the postgres back-end process which
is
using all the CPU time during the query. Doesn't sound like Vector
operations to me, which should cause the java process to eat all the CPU
time.
Rich.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [INTERFACES] JDBC Query performance
@ 1999-03-23 06:51 Postgres mailing lists <postgres@weblynk.com>
1999-03-23 06:48 ` Re: [INTERFACES] JDBC Query performance Peter T Mount <peter@retep.org.uk>
0 siblings, 1 reply; 8+ messages in thread
From: Postgres mailing lists @ 1999-03-23 06:51 UTC (permalink / raw)
To: pgsql-interfaces; +Cc: Peter Mount (Home) <peter@retep.org.uk>
That's what's interesting about this to me. Using psql, piping the results
to a file, it is less than a second, and the CPU time goes to 50% idle. The
file that I piped to ends up about 3.5MB's. Jdbc is about 90 seconds, and 0%
idle. I've put trace statements in, so I'm pretty sure that to return from
the executeQuery(sql); method is about 90 seconds. If you can't reproduce
it, then maybe I could send you the table that I query, or maybe you could
send me a table that you query of similiar size (~1200 rows, 25 columns, no
indexes, most columns are varchar() or float8() ), that it performs well on.
Rich.
>Hmmm, this is interesting. Does the cpu time match when the same queries
>are run through PSQL?
>
>Peter
>
>--
>Peter T Mount, IT Section
>petermount@it.maidstone.gov.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@weblynk.com]
>Sent: Sunday, March 21, 1999 7:11 PM
>To: pgsql-interfaces@postgreSQL.org
>Subject: Re: [INTERFACES] JDBC Query performance
>
>
>>At 11:07 +0200 on 18/03/1999, Peter Mount wrote:
>>
>>
>>>
>>> The delay is because the driver currently retrieves the entire result
>>> into a Vector() before returning the ResultSet.
>>
>>Couldn't it also be because of Java's usage of network sockets compared
>to
>>psql's use of unix sockets?
>>
>You know, I also noticed that it is the postgres back-end process which
>is
>using all the CPU time during the query. Doesn't sound like Vector
>operations to me, which should cause the java process to eat all the CPU
>time.
>Rich.
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [INTERFACES] JDBC Query performance
1999-03-23 06:51 Re: [INTERFACES] JDBC Query performance Postgres mailing lists <postgres@weblynk.com>
@ 1999-03-23 06:48 ` Peter T Mount <peter@retep.org.uk>
0 siblings, 0 replies; 8+ messages in thread
From: Peter T Mount @ 1999-03-23 06:48 UTC (permalink / raw)
To: Postgres mailing lists <postgres@weblynk.com>; +Cc: pgsql-interfaces
On Mon, 22 Mar 1999, Postgres mailing lists wrote:
> That's what's interesting about this to me. Using psql, piping the results
> to a file, it is less than a second, and the CPU time goes to 50% idle. The
> file that I piped to ends up about 3.5MB's. Jdbc is about 90 seconds, and 0%
> idle. I've put trace statements in, so I'm pretty sure that to return from
> the executeQuery(sql); method is about 90 seconds. If you can't reproduce
> it, then maybe I could send you the table that I query, or maybe you could
> send me a table that you query of similiar size (~1200 rows, 25 columns, no
> indexes, most columns are varchar() or float8() ), that it performs well on.
> Rich.
Ok, if you can send me a table direct to my home address (not the list),
I'll see if I can reproduce it.
Peter
>
> >Hmmm, this is interesting. Does the cpu time match when the same queries
> >are run through PSQL?
> >
> >Peter
> >
> >--
> >Peter T Mount, IT Section
> >petermount@it.maidstone.gov.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@weblynk.com]
> >Sent: Sunday, March 21, 1999 7:11 PM
> >To: pgsql-interfaces@postgreSQL.org
> >Subject: Re: [INTERFACES] JDBC Query performance
> >
> >
> >>At 11:07 +0200 on 18/03/1999, Peter Mount wrote:
> >>
> >>
> >>>
> >>> The delay is because the driver currently retrieves the entire result
> >>> into a Vector() before returning the ResultSet.
> >>
> >>Couldn't it also be because of Java's usage of network sockets compared
> >to
> >>psql's use of unix sockets?
> >>
> >You know, I also noticed that it is the postgres back-end process which
> >is
> >using all the CPU time during the query. Doesn't sound like Vector
> >operations to me, which should cause the java process to eat all the CPU
> >time.
> >Rich.
> >
> >
>
--
Peter T Mount peter@retep.org.uk
Main Homepage: http://www.retep.org.uk
PostgreSQL JDBC Faq: http://www.retep.org.uk/postgres
Java PDF Generator: http://www.retep.org.uk/pdf
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~1999-03-23 06:51 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-03-18 08:53 JDBC Query performance Postgres mailing lists <postgres@weblynk.com>
1999-03-18 09:07 RE: [INTERFACES] JDBC Query performance Peter Mount <petermount@it.maidstone.gov.uk>
1999-03-21 14:47 ` Herouth Maoz <herouth@oumail.openu.ac.il>
1999-03-21 17:44 ` Peter T Mount <peter@retep.org.uk>
1999-03-21 19:10 Re: [INTERFACES] JDBC Query performance Postgres mailing lists <postgres@weblynk.com>
1999-03-22 09:24 RE: [INTERFACES] JDBC Query performance Peter Mount <petermount@it.maidstone.gov.uk>
1999-03-23 06:51 Re: [INTERFACES] JDBC Query performance Postgres mailing lists <postgres@weblynk.com>
1999-03-23 06:48 ` Peter T Mount <peter@retep.org.uk>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox