view release on metacpan or  search on metacpan

lib/DBIO/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.

=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|DBIO::ResultSet/prefetch> relationships, where possible. See
L</Using joins and prefetch>.

=item *

Use L<populate|DBIO::ResultSet/populate> in void context to insert data
when you don't need the resulting L<result|DBIO::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|DBIO::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<DBIO::Row>
objects, consider using L<DBIO::ResultClass::HashRefInflator>.

=item *

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

=back

=head1 STARTUP SPEED

L<DBIO|DBIO> 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 build your classes dynamically based on the database schema at
startup then there will be a significant startup delay.

For production use a statically defined schema, which can be generated
using the L<dbiogen> utility (powered by L<DBIO::Generate>) to dump the
database schema once - see L<DBIO::Generate/dump_directory> for more
details on creating static schemas from a database.

=head2 Move Common Startup into a Base Class

Typically L<DBIO> result classes start off with

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

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

    package MyDBICbase;

    use base qw/DBIO::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.

=head2 Explicitly List Schema Result Classes