From: jing han <jing_han_66@yahoo.com>
To: pgsql-interfaces@postgresql.org
Subject: about libpq-C FETCH ALL performance issue
Date: Mon, 15 Nov 2004 05:55:01 -0800 (PST)
Message-ID: <20041115135501.51510.qmail@web53507.mail.yahoo.com> (raw)
help topics
libpq-C FETCH ALL performance issue.
Hi,
I'm working on a project where libpq-C is envolved. We
have several background processes keep querying
certain data from database at a fast rate.
With libpq-C interface, I use
res = PQexec(conn, "DECLARE myportal CURSOR FOR select
* from ourTables");
and then res = PQexec(conn, "FETCH ALL in myportal");
to get the data.
I found when our database has more and more data, res
= PQexec(conn, "FETCH ALL in myportal"); cost more and
more time, much more than
res = PQexec(conn, "DECLARE myportal CURSOR FOR select
* from ourTables");
(note:ourTables can be several tables join together)
and make the fetching rate extremely slow. We get
into big problems here.
For example, when we have 3 primary records and their
related records in database,
res = PQexec(conn, "DECLARE myportal CURSOR FOR select
* from ourTables");
takes 0.0012 sec, and
res = PQexec(conn, "FETCH ALL in myportal");
takes 0.0006 sec, which are fine.
But when we have more than 1000 records in database,
the first function takes 0.0013 sec, but the second
function takes 0.028 sec.
Then I try FETCH 1 instead of FETCH ALL, no
improvement.
All the queries our background processes are using
have been optimized with EXPLAIN utility, these
queries take much shorter time in postgres console.
So I wonder what FETCH statement is doing: just fetch
query results from cursor OR do the real query,
get the query result and give these results to us.
Does FETCH ALL do the real query in the temporary
variable "myportal"? Why it's much slower than console
command.
I also try to tune some parameters in postgresql.conf,
not helpful.
We are using RedHat 9.0 with postgresql-7.3.2-3.
I wonder if there is other faster way ( I mean other
than FETCH ALL command) to get data from database with
libpq-C, or If you have a patch to make FETCH work
faster or new release to work better?
Hope to hear from you soon.
Best Regards
jing
__________________________________
Do you Yahoo!?
Check out the new Yahoo! Front Page.
www.yahoo.com
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-interfaces@postgresql.org
Cc: jing_han_66@yahoo.com
Subject: Re: about libpq-C FETCH ALL performance issue
In-Reply-To: <20041115135501.51510.qmail@web53507.mail.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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