I asked about DBI tracing a while ago

A while ago I asked a question about how do I trace what SQL queries
are being performed. I wanted to improve performance of my site
algebra.com. One of the issues was that I was using some pre-prepared
queries (with "?" marks such as

$sth = dbh->prepare( "...?,?" );

(please do not mention setting trace on on DBI, it does not do what I
need as it does not trace execution of these precompiled statements)

Anyway, I was quite frustrated since I could not know what queries my
website was performing.

I finally wrote a module that wrapped itself around DBI, so that all
function calls except some junk calls like quote() or fetchrow*, were
traced.

The way I use it is that my module that creates or reuses the DB
handle (the only way for me to get a DB handle), now has a debug
option. If debug is not set, it simply returns the dbh. It it is set,
it returns a DebugWrapper wrapped around dbh.

So... Now I know what statements my website was running, and was able
to speed it up 3 times by using better SQL and calling SQL less often.

i

######################################################### Utils
#
#
#
# Copyright: ichudov@xxxxxxxxxxx


use strict;
use warnings;

package Algebra::DebugWrapper;
use vars qw( @ISA $VERSION @EXPORT );

use Carp qw( confess );

@ISA = qw(Exporter);
$VERSION = 2000.0821;

@EXPORT= qw(
new
);

######################################################## implementationbin/

use vars qw(
$ignore
);



sub new {
my ($type, $related, $prefix, $info) = @_;

$info = "" unless defined $info;

#print STDERR "PREFIX=$prefix, type=$type.\n";

my $this = bless { related => $related,
prefix => $prefix,
info => $info
},
$type;

return $this;
}

sub prepare {
my ($this, @args) = @_;

my $sth = $this->{related}->prepare( @args );
return undef unless $sth;

return new Algebra::DebugWrapper( $sth, "SQL", $args[0] );
}

sub AUTOLOAD {
my ($this, @args) = @_;
my $subname = $Algebra::DebugWrapper::AUTOLOAD;
$subname =~ s/^Algebra::DebugWrapper:://;
#print "this=$this. $this->{prefix}: $subname( ) \n";
if( defined $subname && $subname ne 'DESTROY' ) {
#print "subname=$subname.\n";

my $cgi = new CGI;
my $url = $cgi->url;
unless( $ignore->{$subname} ) {
my $msg = "$url Wrapper $this->{prefix}: $subname( " . join( ', ', @args ) . " ) $this->{info}";

if( $this->{info} =~ /^\s*insert\s+into\s*(.*)/ ) {
$msg = "Wrapper $this->{prefix}: $this->{info}";
}

print STDERR "$msg\n";

#print "Keys of this = " . join( ',', keys %$this ) . ".\n";
#print "RELATED=$this->{related}.\n";
}

if( wantarray ) {
my @result;
eval {
@result = $this->{related}->$subname( @args );
};
if( $@ ) {
print STDERR "Error in DebugWrapper ($this->{prefix}"."->$subname $this->{info}\n";
die $@;
}
return @result;
} else {
my $result;
eval {
$result = $this->{related}->$subname( @args );
};
if( $@ ) {
print STDERR "Error in DebugWrapper ($this->{prefix}"."->$subname $this->{info}\n";
die $@;
}
return $result;
}
}

#print "\n\n";
}

$ignore = {
fetchrow => 1,
fetchrow_hashref => 1,
fetchrow_arrayref => 1,
quote => 1,
finish => 1,
};
1;

.

Relevant Pages

  • Re: Problem with column_info/DBD::Oracle 1.21
    ... Looking at the trace I see 1 sub in the SQL and 1 sub happening later on. ... Aktiengesellschaft mit Sitz in D-72285 Pfalzgrafenweiler, ...
    (perl.dbi.users)
  • RE: event procedure with docmd.runsql
    ... In this case, it isn't about "quote happy", it's about formatting the sql so ... Private Sub DoStuff_Click ... Dim lngoper As Long ... two queries via transferspreadsheet to one workbook. ...
    (microsoft.public.access.formscoding)
  • Re: Official Status of SQLServer 2005 ADP
    ... I have said that the support for SQL passthrough ... queries under MDB was bad and worst than the one offered by ADP while you ... > attempt to "pass through" every Access query against a linked ODBC ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Massive Large Query Issues
    ... Project Budgets: Entered at the Capital Request level, ... installed (One install is typically $100,000 plus the cost of the equpment, ... install SQL Server. ... Often, a collection of queries ...
    (microsoft.public.access.queries)
  • Re: Massive Large Query Issues
    ... Project Budgets: Entered at the Capital Request level, ... install SQL Server. ... of queries? ... make of the query you provided is to roll it up into a subsequent ...
    (microsoft.public.access.queries)