Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 8C9F3650038 for ; Mon, 17 Nov 2008 04:26:53 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 80200-02-2 for ; Mon, 17 Nov 2008 04:26:49 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail.morphoss.com (hoppy.mcmillan.net.nz [202.78.240.82]) by mail.postgresql.org (Postfix) with ESMTP id C9C1664FC5C for ; Mon, 17 Nov 2008 04:26:48 -0400 (AST) Received: from [IPv6:2404:130:b055:0:215:afff:fe48:6c48] (unknown [IPv6:2404:130:b055:0:215:afff:fe48:6c48]) by mail.morphoss.com (Postfix) with ESMTPS id 4056F22F35; Mon, 17 Nov 2008 21:26:43 +1300 (NZDT) Subject: Re: [Q] PDO use to bind arrays for insert From: Andrew McMillan To: V S P Cc: pgsql-php@postgresql.org In-Reply-To: <1226896018.14093.1285127497@webmail.messagingengine.com> (sfid-20081117_173237_571180_472D2DD1) References: <1226803530.24888.1284994985@webmail.messagingengine.com> <492093A5.7040606@gmail.com> <20081117002630.d86160fd.knl@bitflop.com> <1226890873.32288.1285117971@webmail.messagingengine.com> (sfid-20081117_160220_914818_FB8966C0) <1226894747.5577.267.camel@happy.mcmillan.net.nz> <1226896018.14093.1285127497@webmail.messagingengine.com> (sfid-20081117_173237_571180_472D2DD1) Content-Type: text/plain Date: Mon, 17 Nov 2008 21:26:42 +1300 Message-Id: <1226910402.5577.280.camel@happy.mcmillan.net.nz> Mime-Version: 1.0 X-Mailer: Evolution 2.22.3.1 Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200811/16 X-Sequence-Number: 3927 On Sun, 2008-11-16 at 23:26 -0500, V S P wrote: > Hi, > thank you Andrew. > > do you know if query planning happens only once > for the prepared Insert statements > where data is fed from within a client loop > > foreach ... > execute prepared insert > end > > and if it happens once -- is it on the first insert? Yes, it happens once on the first one. > > And the last question -- with pgpool > do prepared statements last between > different client connections? > > that is If I have say apache setup with 5 processes > (and therefore PHP can be invoked within 5 worker processes > simultaneously) > > would the Prepared statements last across invocations Prepared statements are per connection, and the actions taken at the close of a connection to pgpool to ready the pool for connection from a different client will very likely deallocate any prepared statements. The developers are usually actively trying to minimise pollution of state from one connection to the next, but persistence of prepared statements might be a different kettle of fish... Read here for more information. In paricular note that PREPARE has it's greatest impact when used on complex SQL - i.e. probably not INSERT statements: http://www.postgresql.org/docs/8.3/interactive/sql-prepare.html If you really need performance on a stream of inserts it probably is better to feed them to a separate daemon to specifically handle them serially, otherwise any peak load on your website is going to also peak load your DB server. If you can stream the inserts separately you can potentially shift that load. Obviously no good if your inserts aren't blind, but when there are large streams of them they often are only looked at statistically at a later time. Your questions here are really getting beyond the scope of the pgsql-PHP list though, and you might want to try asking them on pgsql-general or something where you will have a wider resource of PostgreSQL experts... Cheers, Andrew McMillan. ------------------------------------------------------------------------ andrew (AT) morphoss (DOT) com +64(272)DEBIAN "... all the modern inconveniences ..." -- Mark Twain ------------------------------------------------------------------------