view release on metacpan or  search on metacpan

lib/DBIx/Class/Manual/Cookbook.pod  view on Meta::CPAN

  sub query_end {
    my $self = shift();
    my $sql = shift();
    my @params = @_;

    my $elapsed = sprintf("%0.4f", time() - $start);
    $self->print("Execution took $elapsed seconds.\n");
    $start = undef;
  }

  1;

You can then install that class as the debugging object:

  __PACKAGE__->storage->debugobj(new My::Profiler());
  __PACKAGE__->storage->debug(1);

A more complicated example might involve storing each execution of SQL in an
array:

  sub query_end {
    my $self = shift();
    my $sql = shift();
    my @params = @_;

    my $elapsed = time() - $start;
    push(@{ $calls{$sql} }, {
        params => \@params,
        elapsed => $elapsed
    });
  }

You could then create average, high and low execution times for an SQL
statement and dig down to see if certain parameters cause aberrant behavior.
You might want to check out L<DBIx::Class::QueryLog> as well.

=head1 IMPROVING PERFORMANCE

=over

=item *

Install L<Class::XSAccessor> to speed up L<Class::Accessor::Grouped>.

=item *

On Perl 5.8 install L<Class::C3::XS>.

=item *

L<prefetch|DBIx::Class::ResultSet/prefetch> relationships, where possible. See
L</Using joins and prefetch>.

=item *

Use L<populate|DBIx::Class::ResultSet/populate> in void context to insert data
when you don't need the resulting L<result|DBIx::Class::Manual::ResultClass> objects,
if possible, but see the caveats.

When inserting many rows, for best results, populate a large number of rows at a
time, but not so large that the table is locked for an unacceptably long time.

If using L<create|DBIx::Class::ResultSet/create> instead, use a transaction and
commit every C<X> rows; where C<X> gives you the best performance without
locking the table for too long.

=item *

When selecting many rows, if you don't need full-blown L<DBIx::Class::Row>
objects, consider using L<DBIx::Class::ResultClass::HashRefInflator>.

=item *

See also L</STARTUP SPEED> and L</MEMORY USAGE> in this document.

=back

=head1 STARTUP SPEED

L<DBIx::Class|DBIx::Class> programs can have a significant startup delay
as the ORM loads all the relevant classes. This section examines
techniques for reducing the startup delay.

These tips are listed in order of decreasing effectiveness - so the
first tip, if applicable, should have the greatest effect on your
application.

=head2 Statically Define Your Schema

If you are using
L<DBIx::Class::Schema::Loader|DBIx::Class::Schema::Loader> to build the
classes dynamically based on the database schema then there will be a
significant startup delay.

For production use a statically defined schema (which can be generated
using L<DBIx::Class::Schema::Loader> to dump the database schema once - see
L<make_schema_at|DBIx::Class::Schema::Loader/make_schema_at> and
L<dump_directory|DBIx::Class::Schema::Loader::Base/dump_directory> for more
details on creating static schemas from a database).

=head2 Move Common Startup into a Base Class

Typically L<DBIx::Class> result classes start off with

    use base qw/DBIx::Class::Core/;
    __PACKAGE__->load_components(qw/InflateColumn::DateTime/);

If this preamble is moved into a common base class:-

    package MyDBICbase;

    use base qw/DBIx::Class::Core/;
    __PACKAGE__->load_components(qw/InflateColumn::DateTime/);
    1;

and each result class then uses this as a base:-

    use base qw/MyDBICbase/;

then the load_components is only performed once, which can result in a
considerable startup speedup for schemas with many classes.