Re: Re: mod_perl was hosed by a bind variable

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.

Comment on Re: Re: mod_perl was hosed by a bind variable

Replies are listed 'Best First'.
Re: Re: Re: mod_perl was hosed by a bind variable by fireartist (Chaplain) on Jan 30, 2004 at 18:31 UTC
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]

Replies are listed 'Best First'.

Re: Re: Re: mod_perl was hosed by a bind variable
by fireartist (Chaplain) on Jan 30, 2004 at 18:31 UTC

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]

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]