about libpq-C FETCH ALL performance issue

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