Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer

DBI query count?

by skx (Parson)
on May 20, 2005 at 06:02 UTC ( #458855=perlquestion: print w/replies, xml ) Need Help??

skx has asked for the wisdom of the Perl Monks concerning the following question:

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).

Replies are listed 'Best First'.
Re: DBI query count?
by redhotpenguin (Deacon) on May 20, 2005 at 07:39 UTC
    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.
Re: DBI query count?
by Joost (Canon) on May 20, 2005 at 08:35 UTC
Re: DBI query count?
by Tanktalus (Canon) on May 20, 2005 at 14:52 UTC

    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 ;-)

Re: DBI query count?
by thcsoft (Monk) on May 21, 2005 at 01:45 UTC
    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.

    language is a virus from outer space.

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://458855]
Approved by Roger
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others browsing the Monastery: (2)
As of 2021-07-28 22:09 GMT
Find Nodes?
    Voting Booth?

    No recent polls found