On Tue 23 Nov 2004 03:45, Darren Duncan <[EMAIL PROTECTED]> wrote:
> I am looking for a bit of insight on best / common / intended
> practices concerning prepared statements, as represented by DBI
> statement handles ($sth).
>
> Specifically, is it okay to have a long-lasting statement handle,
Yes it is. Let the DB daemons worry about caching, unless you have a wide
network, then a DBI Proxi and/or prepare_cached () might be helpful
> such as that you create it when your program starts up and keep it
> around until your program quits, so every actual use is just an
> execute()?
>
> Or do statement handles hold on to limited database server resources
> such that it is best to have them for as little time as possible,
> meaning to have all of your execute() as close as possible to the
> prepare() in the program execution?
In that case, like scanning a table, make the statement handle lexical, so you
are sure it is also destoyed after use
my %blah;
{ my ($sth, $k, $v) = ($dbh->prepare ("select key,val from blah"));
$sth->execute;
$sth->bind_columns (\$k, \$v);
while ($sth->fetch) {
$blah{$k} = $v;
}
}
> My question mainly applies to the DBD modules that use real
> database-implemented prepared statements, and not emulated ones that
> don't actually do anything until execute() time. In the latter case,
> I know that no resources are held on to.
prepare takes resources, execute and fetch use them
> I have a decision to make as to whether I should try, with a long
> running program such as would run under mod_perl, to front-load all
> the prepare() to when a process or thread starts, so each page
> request only has execute().
>
> The other option is to run them close together more or less like do() does.
>
> More broadly speaking, I wonder if it is possible to do prepares
> prior to opening a database connection, so they can be reused through
No. As Michael stated, you need Stored procedures and/or triggers for that
> multiple connections? Or if the system was never designed for this
> sort of thing?
>
> If I have a choice to do either, it might be useful to add an
> information function to the DBI interface that reports whether the
> current $dbh will use actual or emulated prepared statements. Unless
> one already exists.
--
H.Merijn Brand Amsterdam Perl Mongers (http://amsterdam.pm.org/)
using perl-5.6.1, 5.8.5 on HP-UX 10.20, 11.00, & 11.11, Cygwin-1.15.11
AIX 4.3.3, AIX 5.2.0, & SuSE 9.1 often with Tk800.024 &/| DBD-Unify
ftp://ftp.funet.fi/pub/languages/perl/CPAN/authors/id/H/HM/HMBRAND/
