homepage Welcome to WebmasterWorld Guest from
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Perl Server Side CGI Scripting
Forum Library, Charter, Moderators: coopster & jatar k & phranque

Perl Server Side CGI Scripting Forum

MySQL - Perl Speed Tips?

 4:46 pm on Mar 2, 2007 (gmt 0)

A while back, I made a comment about how slow I felt the DBI/MySQL module usage was - and got some feedback that it could be speed up a bit. How so? What are the tips to speeding up the other was lathargic connection?



 8:48 pm on Mar 2, 2007 (gmt 0)

I don't know if this is directly relevant but make sure you pool your database handles. Building and tearing down connections really slows things down if you can just keep one open.

I use a sub like this in my classes (sorry for the dropped whitespace):

sub get_dbh {
my $self= shift;
return $self->{'_dbh'} if (ref $self->{'_dbh'});
my $dbh = DBI->connect(@{ $self->{'dbn'} })
$self->error('Unable to connect to Database');
return $self->{'_dbh'} = $dbh;


 12:34 am on Mar 3, 2007 (gmt 0)

have you tried using Benchmark.pm [perldoc.perl.org]?
it's a core perl module.

use Benchmark;
$t0 = new Benchmark;
# ... your code here ...
$t1 = new Benchmark;
$td = timediff($t1, $t0);
print "the code took:",timestr($td),"\n";

if you are doing a repetitive operation you can sum the timediffs to see how much total time you are spending there.
the number one rule in solving efficiency problems is to start with the largest targets.


 1:20 am on Mar 3, 2007 (gmt 0)

if it ends up being a connection problem, you should check out Apache::DBI [search.cpan.org] or even mod_dbd [httpd.apache.org].

i assume you are using or have considered mod_perl [perl.apache.org].


 4:57 am on Mar 4, 2007 (gmt 0)

Reusing database connections are always a good idea. You'll also want to use/re-use prepared statement handlers.

Benchmarking is important but probably not as informative as profiling. My favorite profiling module is Devel::SallProf while SMALLPROF_CONFIG=zg. This will give you profiling information to the line. Then use this script to sort the output with the biggest timesinks first then possibly piped to "less" or "$EDITOR -":

use strict;
use warnings;
# * file name : line number : line count : time (ms) : ctime (ms) : line source
my( @unsorted ) = do {
open( my( $SMALLPROF ), '<', 'smallprof.out' ) or die $!;
local $/;
my( @sorted ) =
map { join( ':', @$_ ) }
sort { $a->[3] <=> $b->[3] }
map { [ split( /:/, $_ ) ] }
print @sorted, "\n";

Global Options:
 top home search open messages active posts  

Home / Forums Index / Code, Content, and Presentation / Perl Server Side CGI Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved