[Dbix-class] Monitoring how many rows are returned per query

[Sam Kington]

Hi,

I'm new to the mailing list, and only quickly skimmed the archives, so apologies if this question has been asked before.

At $WORK, for our Chimera project, we bought fully into the way of prefetching, and that worked fine with our tests that were working on limited data sets. But now we've found that those ever-so-clever queries that prefetched multiple independent tables at a time are now generating hundreds of megabytes of data and thousands of lines of SQL which we don't actually want or need. So we'd like to put a stop to that. And one of the ways is monitoring the number of rows that queries produce in tests, staging or, failing that, production.

Unfortunately, there doesn't appear to be a clean way of doing this. So far my best attempt is a rather nasty monkeypatch:

    my $sql_row_count;
    no warnings 'redefine';
    $old_dbh_execute = \&DBIx::Class::Storage::DBI::_dbh_execute;
    *DBIx::Class::Storage::DBI::_dbh_execute = sub {
        my $self = shift;
        my ($rv, $sth, @bind) = $old_dbh_execute->($self, @_);

	Test::Chimera->inspect_statement_handle($sth);

        return (wantarray ? ($rv, $sth, @bind) : $rv);
    };

    package Test::Chimera;

    my $sql_row_count;
    sub inspect_statement_handle {
        my ($sth) = @_;

        my $sql_statement = $sth->{Statement};
        if (Database::Chimera->schema->storage->debug
            && $sql_statement !~ /^ (INSERT \s INTO | UPDATE ) \s log \s /x)
        {
            $sql_row_count += $sth->rows;
            if ($ENV{VERBOSE_SQL_ROW_COUNT}) {
                printf STDERR
                    "# %s\nwith bind [%s]\n",
                    $sql_statement, join('|', map { $_->[1] } @bind);
                print STDERR "# Got back " . $sth->rows . " rows\n";
            }
        }
    }

The problem is that, at least in the version current on the CPAN, _dbh_execute does the following:

     $self->_query_start($sql, $bind);
    my $sth = $self->_bind_sth_param(...);
    my  $rv = $sth->execute;
    $self->throw_exception(...) if !$rv;
    $self->_query_end($sql, $bind);
    return (wantarray ? ($rv, $sth, @$bind) : $rv);

Any debugging objects get the query and bind arguments - the what of the query - but not the how - how many rows were returned, for instance, or anything else you can get from the DBI statement handle.

Maybe it could be worth adding e.g. $self->_query_details($sth) which would call the appropriate debugger object?

Or is there a better way of capturing this information for debugging? I chose _dbh_execute as the method to monkey-patch as it seemed the most obvious, but I don't know the guts of DBIx::Class at all well so I could certainly have missed something.

Thanks in advance.

Sam
-- 
Website: http://www.illuminated.co.uk/