DBI query count?

[skx]

In my CGI::Application I use DBI - connecting to the database as it starts, disconnecting at the end.

During the course of the different run modes a different number of SQL queries are executed.

Is it possible to have a count of the "number of queries" executed in some simple manner?

Right now the only approach seems to be to subclass DBI, and have a counter which is incremented in 'execute' or 'query' methods, is that correct?

(I'm looking to have something which I've seen a lot of PHP applications do; show at the bottom of the page "Generation took xx queries and xx seconds". This will also help me keep track of potential database slowpoints).

[redhotpenguin]

Subclassing execute() and query() methods within your CGI::Application seems like the right approach for a request based counter. But tracking database slowpoints should be done via the database logs. Most databases will allow you to log any query which takes longer than a specified time period, which you can set in the configuration file. Here's an example from my Postgresql database conf file:

log_min_duration_statement = 420 # -1 is disabled, in milliseconds.

[Joost]

This is not exactly what you're asking, but you probably could get the DBI trace to show you information on the connections, queries etc: see the TRACING section in the DBI manual

[Tanktalus]

Off the top of my head, I would say this would be trivial to do. But you may not write your code the way I write mine ;-).

package My::App;
use base 'CGI::Application';
use DBI;
#...

sub _connect
{
  my $self = shift;
  my $dbh = $self->param('dbh');
  return $dbh if $dbh;

  my $dsn = $self->param('dsn');
  # user, password...
  $dbh = DBI->connect($dsn, ...);
  $self->param('dbh' => $dbh);
  $dbh;
}

sub _prepare
{
  my $self = shift;
  my $dbh  = $self->_connect();
  $dbh->prepare(@_);
}

sub _execute
{
  my $self = shift;
  my $sth  = shift;
  $self->{_execute}++; # here it is...
  $sth->execute(@_);
}

I route all my DBI calls through functions like these simply to save time and effort later when (not if) I want to do something wierd. For example, passing in specific parameters to the prepare, or, in your case, counting the executions. You can even put some calls to Time::HiRes around that execute and total up the time for the queries that way.

I also like delaying my connections until I really need them. That way, if a given mode doesn't need to touch the database, then I don't waste time, cycles, or whatever, in creating that connection. Not only am I lazy, but so are my programs ;-)

[thcsoft]

another approach, if checking the logs or working around come out to be too much time consumptive for you, might be found in the show status; query to the database and grepping out the values you're looking for.

use vars 'QUERY_COUNT';
...
my $res = $dbh->selectrow_hashref("show status");
$QUERY_COUNT += ($res->{'Questions'} - $QUERY_COUNT);

maybe that's a silly suggestion, but there are quite trivial ways of getting metadata from a database.