On Mon, 22 Nov 2004 18:45:21 -0800, 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, > 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()?
It's OK - but beware of problems if the tables referenced by the statement change structure (eg ALTER TABLE) after the staement is prepared and before it is used. Obviously, in a server-managed prepared statement, there is some memory used in holding the statement ready to go. > 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? If your server is so limited for resources that a single prepared statement throws it off, then you've got problems independent of whether you hold statement handles. If you have hundreds of applications, each of which prepares hundreds of statements, then you can have problems even on abundantly provisioned servers. > 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. > > 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. A third option is to cache the most frequently used statement handles and to prepare new statements as required. The fun comes in deciding how to push handles that were once heavily used out of the cache when the access pattern changes - you need to manage that carefully. > More broadly speaking, I wonder if it is possible to do prepares > prior to opening a database connection, so they can be reused through > multiple connections? Or if the system was never designed for this > sort of thing? Well, if a database is going to prepare the statement, you have to be connected to the database (or, at least, the DBMS) in order to even think about preparing it. You can't have the server do anything with a statement until you've got some connection to it. > 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. Ask Tim nicely - and expect a lag while drivers catch up. -- Jonathan Leffler <[EMAIL PROTECTED]> #include <disclaimer.h> Guardian of DBD::Informix - v2003.04 - http://dbi.perl.org "I don't suffer from insanity - I enjoy every minute of it."
