One of the things you could do is to subclass the _mk_column_accessors method, e.g.:

sub _mk_column_accessors {
    my $class = shift;
    my @new = grep {!exists &{$class."::$_"}} @_; # remember new ones
    $class->SUPER::_mk_column_accessors( @_ ); # accessors + mutators

# Redefine accessors to be very lean

    no warnings 'redefine';
    no strict 'refs';
    foreach (@new) {
        my $method = $class."::$_";
        my $old = \&$method;
        *$method = sub {
# if not available yet, got fetch with normal accessor
            return $old->( @_ ) unless exists $_[0]->{$_};
            $_[0]->{$_};
        };
    }
} #_mk_column_accessors
[download]

Further optimizations with the accessors could be made if you know 100% sure that when a subclss Class::DBI object is created, all the fields are fetched from the database (bij setting all of the fields to be "Essential"). Something like:

sub _mk_column_accessors {
    my $class = shift;
    eval "sub  ${class}::$_ { \$_[0]->{'$_'} }"
     foreach grep {!exists &{$class."::$_"}} @_;
    $class->SUPER::_mk_column_accessors( @_ );
} #_mk_column_accessors
[download]

Please note that I've used a string eval here for a reason: benchmarks have shown that accessors created that way are the fastest accessors possible (just as if writing them manually). All other solutions use a closure, which are inherently slower. And since speed is what you're interested in here... ;-)

Hope this helps.

Liz

Forgot the -r flag, yes. Posted an update to OP. Thanks.

Maybe you should explain what your application does. These are certainly not typical numbers for a DBI app. For example, your ping is really high up there. Typically, the DBI execute method would be way up at the top. Are you not resuing your database connections?

In general, Class::DBI (or any other O/R mapper) is going to have poor performance when used for bulk loading. To get really good performance on that stuff, you need to use your database's bulk loading tools.

You have a variety of strategies available to you. Probably one of the most significant is to look at your actual queries and determine if you can optmize them by pushing all of a query into the owning class instead of allowing Class::DBI to span classes. This is a common and acceptable strategy. However, it does look like the bulk of your time is not spent on database access.

Right off the bat, we see that name_lc is taking a lot of time. That looks like a ridiculously large amount of time spent lower-casing a darned field name. Looking at the code, we see this:

use overload
        '""' => sub { shift->name_lc },
        fallback => 1;
                                                                      
+                                                     
sub name_lc { lc shift->name }
[download]

We have one too many method calls. Eliminating the overload would be great, but if we can't, how about:

use overload
        '""' => sub { lc shift->name },
        fallback => 1;
[download]

The only other place in the code that name_lc was being used was in Class/DBI.pm (and in only one spot). While it's nice to have a single point to lower-case the name, it's not as if we have exotic logic here, so if this solution seemed reasonable, perhaps submitting a patch to the Class::DBI list (assuming that you can demonstrate that this is a common case).

Still, I'd be concerned about these figures. The database is usually the bottleneck and that doesn't appear to be the case in your code.

• To profile (using bash): DBI_PROFILE=DBI::ProfileDumper perl <your program here>
• To analyze: dbiprof