Re: Allow pooled connections to list all prepared queries

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


Christopher Kings-Lynne wrote:
> Say we have a query that takes 350ms to get threads in a forum, and half
> of that is planning time.  Any particular PHP process does not know if a
> previous process has already prepared that query or not.  So I have to
> prepare it every time, deal with errors, etc.

With DBI, this is an ideal case for $dbh->prepare_cached(), which avoids
all the network trips altogether. It pretty much does this:

my %sth;
my $SQL = "SELECT door_number_three()";
if (!exists $sth{$SQL}) {
        $sth{$SQL} = $dbh->prepare($SQL);
}
$sth{$SQL}->execute();

PHP should have something similar.

A far better strategy, and one I often use myself for persistent connections
(e.g. mod_perl) is to prepare all the common queries once in the BEGIN block,
and then use prepare_cached for the lesser used ones.


David Brown wrote:
> DBD:::Pg, in fact, ships with server-side prepares totally turned off. I
> have some code that fixes that for the SELECT and DELETE cases, but it, like
> the rest of this stuff, isn't really release-quality yet

Actually, DBD::Pg "ships" with no server-side support at all. The next version
(1.40, about to be released) does have full server-side support, and when
used with prepare_cached above, should solve all of the problems mentioned
in this thread (for Perl people anyway!), with the exception of being able to
see what has already been prepared. That, however, is something application
writers should not have to worry about, which is why DBI and DBD::Pg will do
all the creation, naming, tracking, and deletion of prepared statements for
you behind the scenes.

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200501020913
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8

-----BEGIN PGP SIGNATURE-----

iD8DBQFB2AMFvJuQZxSWSsgRAjcjAJ9ncSAa2N77iY9xOpyE50sbeuqzjACguKBe
HGWkn+Vv/pKKdyFiipDpdGs=
=FOAg
-----END PGP SIGNATURE-----