in reply to mod_perl was hosed by a bind variable

Here's some benchmark code which shows there's not much difference at all using bind values.

I suspect tye's explanation above is correct.

Results 
> ./test.pl
Benchmark: timing 50000 iterations of bind, inline...
      bind: 53 wallclock secs (23.00 usr +  8.93 sys = 31.93 CPU)
    inline: 52 wallclock secs (21.68 usr +  8.98 sys = 30.66 CPU)

[download]
Code 
#!/usr/bin/perl -w
use strict;
use DBI;
use Benchmark qw/timethese/;
use Config::File;

my $c = Config::File->new( file => 'cppsu.config' );

my $h = DBI->connect( $c->db_datasource, $c->db_user,
                      $c->db_pass, { $c->db_args } );

timethese( 50000, {
  bind   => 'xbind',
  inline => 'xinline',
});

sub xbind {
  my $sql = 'SELECT edited FROM faq WHERE id = ?';
  my $sth = $h->prepare( $sql );
  $sth->execute( 1 );
  
  my @res = $sth->fetchrow_array;
  die unless @res == 1;
}

sub xinline {
  my $sql = 'SELECT edited FROM faq WHERE id = 1';
  my $sth = $h->prepare( $sql );
  $sth->execute;
  
  my @res = $sth->fetchrow_array;
  die unless @res == 1;
}

[download]

Replies are listed 'Best First'.
Re: Re: mod_perl was hosed by a bind variable
by perrin (Chancellor) on Jan 30, 2004 at 14:56 UTC
    If you switch to using prepare_cached for the xbind version, it should make a difference. It would not be possible to use prepare_cached for the xinline version in most cases, since that would results in a new query being cached and held in memory for every possible value of id. Also, you should actually do the variable substitution in xinline to make this realistsic, i.e. "id = $id".
[reply]
      I know that prepared_cache would make a big difference, but I wasn't interested in testing that, just the queries that were shown in the root node.
      Thanks for pointing it out though.
[reply]
Re: Re: mod_perl was hosed by a bind variable
by phildog (Novice) on Jan 30, 2004 at 15:52 UTC
    Your benchmark is interesting, but not very relevant...

    Note the subject: "mod_perl was hosed by a bind variable"

    I'm not claiming bind variables alone make any difference in query speed, I'm claiming that bind variables in conjunction with mod_perl (under versions noted above) make a HUGE difference.

    So why don't you try again? Note: your test will probably need to split into two separate files to measure correctly as run under mod_perl.

[reply]
      Unfortunately I don't have access to a mod_perl server, however it's reasonable to assume initially that it's not a mod_perl specific problem, unless you also ran benchmarks on a non-mod_perl server; remove layers of complexity to isolate the problem.

      I have run some more benchmarks, first I changed the WHERE clause to use a text column and tried it using bind and using non-quoted inline, such as tye suggested was a syntax error.

      This actually resulted in using bind being faster.

      Results 

      > ./test.pl 
Benchmark: timing 50000 iterations of bind, inline...
      bind: 55 wallclock secs (19.04 usr +  8.97 sys = 28.01 CPU)
    inline: 81 wallclock secs (20.21 usr + 11.32 sys = 31.53 CPU)

      [download]
      Code 
      sub xbind {
  my $sql = 'SELECT edited FROM faq WHERE q = ?';
  my $sth = $h->prepare( $sql );
  $sth->execute( 'q' );
  
  my $res = $sth->fetchall_arrayref;
  $xbind = @{$res};
}

sub xinline {
  my $sql = 'SELECT edited FROM faq WHERE q = q';
  my $sth = $h->prepare( $sql );
  $sth->execute;
  
  my $res = $sth->fetchall_arrayref;
  $xinline = @{$res};
}

      [download]
      So, I modified it to check how many rows were being returned, the bind version was returning 1, the inline version was returning all rows.

      My mysql version is 3.23.49, DBI version 1.30 - completely different generation of db than you, so tests are not equal.
      So if you got the same results with these tests, then you should try adding back in layers such as mod_perl, to see which is to cause.

[reply]
[d/l]
[select]

In Section Seekers of Perl Wisdom