dbi statement handlers as package globals in mod_perl script

Discussion in 'Perl Misc' started by Andrew S, Apr 6, 2005.

  1. Andrew S

    Andrew S Guest

    I am working to enhance the performance of a few mod_perl scripts on
    some clients' web sites. One of the optimizations that I have read
    about is to store DBI statement handles as package globals, so that
    they can be re-used within the same process. My concern is that
    something bad may happen if/when a database connection is dropped. I
    realize that Apache::DBI will handle dropped connections gracefully,
    but all statement handles prepared from the original database handle
    will become invalid, won't they?

    Here is some code:

    ========
    use Apache::DBI;
    use strict;
    use vars($dbh $sth);
    ....
    $dbh ||= DBI->connect(...);
    $sth ||= $dbh->prepare("select * from tbl where id = ?");
    ....
    $sth->execute($id);
    @result = $sth->fetchrow_array;
    ....
    ========

    The idea here is to initialize a database handle and a statement handle
    once when the script is first loaded by a process. Thereafter, any web
    requests handled by the same process can re-use the statement handle.
    To the best of my knowledge, this is the most optimal solution. If I
    initialized the statement handle every time the code was run, then I
    would incur the overhead of calling the prepare method every time. But
    as I said, I am a little paranoid about what would happen to $sth if
    the database connection associated with $dbh has to be re-established.

    Has anyone ever used package-global statement handles successfully? If
    so, can you tell me whether the above code is right. Otherwise, what
    recommendations would you make?
    Andrew S, Apr 6, 2005
    #1
    1. Advertising

  2. Andrew S wrote:

    > I am working to enhance the performance of a few mod_perl scripts on
    > some clients' web sites. One of the optimizations that I have read
    > about is to store DBI statement handles as package globals, so that
    > they can be re-used within the same process. My concern is that
    > something bad may happen if/when a database connection is dropped. I
    > realize that Apache::DBI will handle dropped connections gracefully,
    > but all statement handles prepared from the original database handle
    > will become invalid, won't they?


    Just use prepare_cache() and let someone else worry about that.
    Brian McCauley, Apr 6, 2005
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Pablo S
    Replies:
    0
    Views:
    754
    Pablo S
    Sep 1, 2004
  2. tedsuzman
    Replies:
    2
    Views:
    7,080
    Michel Claveau, résurectionné d'outre-bombe inform
    Jul 21, 2004
  3. Ted
    Replies:
    1
    Views:
    462
    Duncan Booth
    Jul 22, 2004
  4. M2

    mod_perl, OO and Globals

    M2, Oct 8, 2003, in forum: Perl Misc
    Replies:
    3
    Views:
    97
    pkent
    Oct 9, 2003
  5. Replies:
    2
    Views:
    449
    Big and Blue
    Jan 25, 2006
Loading...

Share This Page