A
Andrew S
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:
BI 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:BI;
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?
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:
BI 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:
BI;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?