Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

use Perl Log In

Log In

[ Create a new account ]

Ovid (2709)

Ovid
  (email not shown publicly)
http://publius-ovidius.livejournal.com/
AOL IM: ovidperl (Add Buddy, Send Message)

Stuff with the Perl Foundation. A couple of patches in the Perl core. A few CPAN modules. That about sums it up.

Journal of Ovid (2709)

Monday December 02, 2002
03:05 PM

Sometimes I feel stupid

[ #9236 ]

Working on a site where performance is becoming a problem. Much of that is in the site design, but a lot of that is in my code. As the work is ongoing, I am getting plenty of experience refactoring, but this morning, it finally dawned on my that I had programs instantiating multiple database handles without good reason. Now, by adding only three lines of code, I've converted the database handle to a singleton and it works perfectly.

If you're not familiar with singletons, they are a "Design Pattern" whereby you only allow one instantiation of a particular object. In this case, with one exception, I don't need separate database handles for every object. As everything goes through one database API, I check to see if I already have a handle. If so, I return that handle rather than create a new one, unless different permissions are needed.

use Test::More tests => 3;
use constant MODULE => 'Foo::Database';

can_ok( MODULE, 'new' );
my $db1 = Foo::Database->new;
isa_ok( $db, MODULE );
my $db2 = Foo::Database->new;
is( $db1->{_dbh}, $db2->{_dbh},
  '... and it should return a singleton database handle' );

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.
  • I learned to avoid that long ago, but then maybe I've been doing DBI longer. I also learned to quit using interpolated statements by using 'execute()' properly (and thus avoiding the unnecessary recreation of execution plans), which can also make a big performance difference. After that it's SQL tuning - ick.

    Coincidentally, I'm having to deal with someone else's code at the moment which is running our database out of connections.

  • Other way is to use Ima::DBI [cpan.org] which I'm quite fond of.
  • If you're doing this in a web environment, you can just use Apache::DBI, and it will make sure that only a single connection exists for each Apache child for you (assuming you use the same connection arguments every time.

    --David

  • djberg96 wrote: I learned to avoid that long ago, but then maybe I've been doing DBI longer.

    It's quite possible that you've been using DBI longer, but I've been using it for three or four years now, so I can't use that as an excuse. The real problem here is simple: I'm rather mediocre in terms of experience. I understand logic well and, as a result, I found that I had an ability to write spaghetti code that ran relatively bug-free compared to code of many of my coworkers. However, it's only been in

  • Depending on how ofter a statement is used you might find the prepare_cached() call useful. It really helped one of my programs.

    $sth = $dbh->prepare_cached($statement);

    Be careful though, you'll want to create your statement with parameters rather than using variable interpolation otherwise you are caching statements that you might not want to cache.

    my $sql = "SELECT lname FROM cust WHERE lname = ?";
    $sth->execute($lastname);

    rather than

    my $sql = "SELECT lname FROM cust WHERE lname = $lastn

  • You've also just improved the security of your site by using not using interpolated variables. Imagine what the user could pass in to $lastname if you're not careful.

    -Dom

    • I've improved the security of the site by ensuring that I don't forget to untaint or use DBI->quote. Those I have used religiously to ensure that nothing nasty can occur. I never directly interpolate user-supplied data in an SQL statement. Whatever other problems occur with my code, security isn't the largest (though I realize that there are always security holes).

      We had another programmer here who didn't bother about security. I tried to explain to him how database security works in conjunction wi