in reply to mod_perl was hosed by a bind variable

Happening to have a mod-perl enabled Apache 2 handy, I tried the following script:

#! x:/xampp/perl/bin/perl.exe use strict; use DBI; use CGI qw/:standard/; use Benchmark qw/timethese/; my $dbh=DBI->connect("DBI:mysql:pandirecords",); my $cgi = new CGI; print $cgi->header; print $cgi->start_html('DBI & mod-perl'); print '<pre>'; timethese( 10000, { inline => 'xinline', bind => 'xbind', }); print '</pre>'; print end_html; sub xbind { my $parameter='0000284364'; my $sql = 'SELECT insured_name FROM claims WHERE ref_underwriter = ?' +; my $sth = $dbh->prepare( $sql ); $sth->execute( $parameter ); my @res = $sth->fetchrow_array; } sub xinline { my $parameter='0000284364'; my $sql = 'SELECT insured_name FROM claims WHERE ref_underwriter ="' +. $parameter .'"'; my $sth = $dbh->prepare( $sql ); $sth->execute; my @res = $sth->fetchrow_array; }

It had the following result:

cgi-bin Benchmark: timing 10000 iterations of bind, inline... bind: 7 wallclock secs ( 2.56 usr + 0.65 sys = 3.21 CPU) @ 31 +12.36/s (n=10000) inline: 7 wallclock secs ( 2.60 usr + 0.50 sys = 3.11 CPU) @ 32 +20.61/s (n=10000)

and

mod-perl Benchmark: timing 10000 iterations of bind, inline... bind: 7 wallclock secs ( 3.02 usr + 0.61 sys = 3.63 CPU) @ 27 +51.03/s (n=10000) inline: 8 wallclock secs ( 3.10 usr + 0.62 sys = 3.72 CPU) @ 26 +91.07/s (n=10000)

The results are a bit unexpected in that the mod-perl script seems to run a bit slower than the cgi-bin script. I guess this is due to the fact that both the browser, Apache and MySQL were running on the same machine and that the major part of the time was anyhow spent in fetching the records, rather than executing the perl-script.

Anyhow, I could not find any significant slowdown between the 'bind' and the 'inline' version of the script, so I must conclude that the huge difference you found must come from somewhere else.

CountZero

"If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law

Replies are listed 'Best First'.
Re: Re: mod_perl was hosed by a bind variable
by dws (Chancellor) on Jan 31, 2004 at 02:41 UTC
    I could not find any significant slowdown between the 'bind' and the 'inline' version of the script, so I must conclude that the huge difference you found must come from somewhere else.

    Note that there's a subtle, but possibly highly significant, difference between the way you're generating your inline query and the way the OP generates his.

      Yes, but it was my intention to check whether there was a difference between the 'bind' and the 'inline' versions over cgi and mod-perl execution using very similar syntax in both versions, the only difference being the use of the '?' placeholder.

      I did not expect any difference (placeholders being emulated in DBD:MySQL), nor could I indeed find any significant difference, leading me to conclude that there is no interference between cgi/mod-perl as far as using place-holders is concerned.

      So the reason must be found somewhere else.

      Update: I tried using the $sth->bind_param(1, $parameter) to see if it made a difference: nope, all remained the same (within the to be expected statistical variations).

      So either it must be a data-issue (seems rather far-fetched) or something with the OP's code (but we haven't seen it, so I cannot comment).

      CountZero

      "If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law

Re: Re: mod_perl was hosed by a bind variable
by CountZero (Bishop) on Jan 31, 2004 at 08:09 UTC
    Thinking that perhaps there was any influence of the MySL results cache, I changed the queries to SELECT SQL_NO_CACHE insured_name FROM claims WHERE ... so as to "switch off" the cache for this query.

    The results did not change (perhaps because the query already was very optimised and used the primary key to find the unique result row).

    CountZero

    "If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law