X-Original-To: pgsql-php-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 25B1DD1B39A for ; Tue, 15 Jun 2004 01:50:47 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 19939-02 for ; Tue, 15 Jun 2004 04:50:46 +0000 (GMT) Received: from mpls-qmqp-04.inet.qwest.net (mpls-qmqp-04.inet.qwest.net [63.231.195.115]) by svr1.postgresql.org (Postfix) with SMTP id 8D1E8D1B2BD for ; Tue, 15 Jun 2004 01:50:42 -0300 (ADT) Received: (qmail 88310 invoked by uid 0); 15 Jun 2004 04:50:42 -0000 Received: from mpls-pop-12.inet.qwest.net (63.231.195.12) by mpls-qmqp-04.inet.qwest.net with QMQP; 15 Jun 2004 04:50:42 -0000 Received: from 63-227-127-37.dnvr.qwest.net (HELO ?10.0.0.2?) (63.227.127.37) by mpls-pop-12.inet.qwest.net with SMTP; 15 Jun 2004 04:50:42 -0000 Date: Mon, 14 Jun 2004 22:51:26 -0600 Message-Id: <1087275086.1582.1.camel@localhost.localdomain> From: "Scott Marlowe" To: "ljb" Cc: pgsql-php@postgresql.org Subject: Re: pg_fetch_array In-Reply-To: References: Content-Type: text/plain Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200406/37 X-Sequence-Number: 2811 On Mon, 2004-06-14 at 18:04, ljb wrote: > Thanks@verymuch.com wrote: > > Hello.... > > > > I would like to know the performance of pg_fetch_array. Cosider the code: > > > > $query = "select * from foo"; > > $result = pg_query( $db, $query ); > > > > while ($row = pg_fetch_array($result)) > > { > > $a = $row["a"]; > > $b = $row["b"]; > > $c = $row["c"]; > > $d = $row["d"]; > > } > > > > Does php need to read database everytime when pg_fetch_array is executed in > > the while loop or all the rows have been in the memory after pg_query? > > The whole result query is stored in memory when pg_query returns, and > pg_fetch_array just gets the next row. > > > If read database is needed, is there any method to copy all the things into > > memory by using other command? (because I have a application which needs > > large amount database update/retrieval and I wish the performance of the > > overall applications run faster.) > > > > or other method you would like to recommend in order to make the faster > > response time? > > Actually, bringing the whole result into memory before processing any rows > can be slower, since the web server has to wait for the database server to > finish before it can start working on the data. So you can't get any > overlap of database and web server processing. As far as I know, there > isn't any alternative currently in the PHP PostgreSQL interface; there are > asynchronous queries but you still can't process any rows until they all > come back. Yes, operating on the whole set in memory at one time is not the best job for PHP, but maybe for PostgreSQL. And there is a way to retrieve some small percentage at a time, use a cursor. See declare and fetch in the manual. Works a charm in PHP.