Comment: DomainKeys? See http://antispam.yahoo.com/domainkeys
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	b=6obUUgZNTkqnTEI6/J7Yp0GgIgookrYP1O+5wFiGdTjyrI/xhK29HA+NUNCcxrhAI2qyZJVi/Ab9szThg3i2jsT56KNFeP6PkqZ6PnUVgf1/EiEqR3VtwPbrMolzkwgOyRHbe0P1saLWHutvwmUlUhIr2WF7Y3KV9LMDLZqBYok=
	;
Message-ID: <20041115135501.51510.qmail@web53507.mail.yahoo.com>
Date: Mon, 15 Nov 2004 05:55:01 -0800 (PST)
From: jing han <jing_han_66@yahoo.com>
Subject: about libpq-C FETCH ALL performance issue
To: pgsql-interfaces@postgresql.org
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

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