pg.ddx.io pgsql-interfaces@postgresql.org mailing list archivehelp / color / mirror / Atom feed
about libpq-C FETCH ALL performance issue 3+ messages / 2 participants [nested] [flat]
* about libpq-C FETCH ALL performance issue @ 2004-11-10 22:09 jing han <jing_han_66@yahoo.com> 0 siblings, 0 replies; 3+ messages in thread From: jing han @ 2004-11-10 22:09 UTC (permalink / raw) To: pgsql-interfaces Hello, I'm working on a project where libpq-C is envolved. We have several background processes keep querying certain data from database and send them out as ip packets. With libpq-C interface, I have to use res = PQexec(conn, "DECLARE myportal CURSOR FOR select * from pg_database"); 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 pg_database"); and make our sending packet rate extremely low. We get into big problems here. 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 fix this problem. Hope to hear from you soon. Best Regards jing __________________________________ Do you Yahoo!? Check out the new Yahoo! Front Page. www.yahoo.com ^ permalink raw reply [nested|flat] 3+ messages in thread
* about libpq-C FETCH ALL performance issue @ 2004-11-15 13:55 jing han <jing_han_66@yahoo.com> 2004-11-15 14:24 ` Re: about libpq-C FETCH ALL performance issue Jeroen T. Vermeulen <jtv@xs4all.nl> 0 siblings, 1 reply; 3+ messages in thread From: jing han @ 2004-11-15 13:55 UTC (permalink / raw) To: pgsql-interfaces 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: about libpq-C FETCH ALL performance issue 2004-11-15 13:55 about libpq-C FETCH ALL performance issue jing han <jing_han_66@yahoo.com> @ 2004-11-15 14:24 ` Jeroen T. Vermeulen <jtv@xs4all.nl> 0 siblings, 0 replies; 3+ messages in thread From: Jeroen T. Vermeulen @ 2004-11-15 14:24 UTC (permalink / raw) To: jing han <jing_han_66@yahoo.com>; +Cc: pgsql-interfaces 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2004-11-15 14:24 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2004-11-10 22:09 about libpq-C FETCH ALL performance issue jing han <jing_han_66@yahoo.com> 2004-11-15 13:55 about libpq-C FETCH ALL performance issue jing han <jing_han_66@yahoo.com> 2004-11-15 14:24 ` Jeroen T. Vermeulen <jtv@xs4all.nl>
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