Обсуждение: 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 

On Mon, Nov 15, 2004 at 05:55:01AM -0800, jing han wrote:
> 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 

If you use just the FETCH ALL, why are you using a cursor in the first
place?


> Then I try FETCH 1 instead of FETCH ALL, no
> improvement.
Are you doing just one FETCH 1 to obtain a single row, or are you
doing the FETCH 1 in a loop so you get all rows?  The latter would
be extremely slow in any case...  Try fetching smaller blocks, like
maybe 100 rows at a time, and see if that helps.


> 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?

Just do a normal "SELECT" without the cursor.  This will give you
all the data at once, just like a FETCH ALL.


Jeroen