Unexpected Benchmark results with DBI-placeholders

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

I've inherited some code at work that does some database updates. Basically does a $dbh->do() in the middle of a loop. Thousands of times. For testing, I just redirected the Mail output (w/sendmail) to a text file.

#old.pl
  while (@emails = $sth->fetchrow) {
    $id       = $emails[0];
    $subject  = $emails[1];
    $body     = $emails[2];
    $mailto   = $emails[3];
    $mailfrom = $emails[4];
    $type     = $emails[5];

    push (@emailsToSend,"To: $mailto\nFrom: $mailfrom\nSubject: $subje
+ct\n\n$body\n");
    push (@emailIds,$id);
    push (@fromAddys,$mailfrom);

    if ($type eq 'weekly') {
      $totalweekly++;
     }
    elsif ($type eq 'daily') {
      $totaldaily++;
    }
    elsif ($type eq 'blast') {
      $totalnewsBlast++;
    }
    elsif ($type eq 'news') {
      $totalnews++;
    }
  }

  $emailcount = 0;
  foreach $singleEmail (@emailsToSend) {
    # SEND THE EMAIL
    $mailfrom = $fromAddys[$emailcount];
        open (OUT, ">>oldEmail.txt");
      print  OUT $singleEmail;
    close(OUT);
    $dbh->do(qq[UPDATE shawnTest set sent = 'Y' WHERE emailId=$emailId
+s[$emailcount]]) ||
&ErrorAlert("500E emailmonitor.pl DBI do error: ".DBI->errstr);
    $emailcount++;
  }
[download]

This seemed like quite a bit of overhead, with the extra variable assignment, as well as the DBI->do, so I did something like this:

#new.pl
    my %total=map{$_,0}(qw(weekly daily blast custom));    
    my $updateSql=qq[UPDATE shawnTest set sent='Y' WHERE emailId=?];
    my $update=$dbh->prepare($updateSql);


while(@emails = $sth->fetchrow_array())
    {
        open (OUT, ">>newEmail.txt");
        print OUT "To: $emails[3]\nFrom: $emails[4]\nSubject:$emails[1
+]\n\n$emails[2]\n";
        close OUT;
        $update->execute($emails[0]);
        $total{$emails[5]}++;
    }
[download]

In both files, this code lives in a sub called fetchemail. I added some code like this to both files.

my $t0 = new Benchmark;
fetchmail();
my $t1 = new Benchmark;
my $td = timediff($t1, $t0);
print "the code took:",timestr($td),"\n";
[download]

perl old.pl
the code took: 9 wallclock secs ( 1.43 usr 1.55 sys + 0.02 cusr 0.02 csys = 0.00 CPU)

#Reset database perl new.pl
the code took: 9 wallclock secs ( 1.59 usr + 1.58 sys = 3.17 CPU)

This code did 10,000 updates, so I didn't feel it necessary to Benchmark it over a thousand times or so. But I did expect, in light of the placeholders, for the results to be a bit more different. Is it my understanding of Benchmark (lack thereof), or do I underestimate the power of placeholders?

ÅßÅ×ÅßÅ
"It is a very mixed blessing to be brought back from the dead." -- Kurt Vonnegut

Comment on Unexpected Benchmark results with DBI-placeholders Select or Download Code

Replies are listed 'Best First'.
Re: Unexpected Benchmark results with DBI-placeholders by perrin (Chancellor) on Jul 21, 2002 at 22:25 UTC
Are you using Oracle? Oracle does some tricks behind the scenes to make things work as if you had used bind variables even when you don't. It's not as good as real bind variables in all cases, but it might account for what you're seeing here. (This is discussed in the latest version of Tim Bunce's DBI talk which you can find on CPAN.) If you really want to speed things up, change that fetchrow_array() to fetchrow_arrayref() or use bind_columns().	[reply]
Re: Unexpected Benchmark results with DBI-placeholders by gav^ (Curate) on Jul 21, 2002 at 23:11 UTC
If you are using a database like MySQL, that doesn't support placeholders, your benchmark isn't really surprising as DBI emulating it is probably no faster than a simple string concatenation. What might be faster, is using bind_columns to save yourself some copying: `$sth->execute(); $sth->bind_columns(\my $id, \my $subject, \my $body, \my $mailto, \my $mailfrom, \my $type); while ($sth->fetch) { # loop as before }` [download] What I'm interested in, but haven't had time to play with is the new functionality added in DBI ver 1.30 (docs) for `fetchall_arrayref`: $tbl_ary_ref = $sth->fetchall_arrayref( $slice, $max_rows ); If $max_rows is defined and greater than or equal to zero then it is used to limit the number of rows fetched before returning. fetchall_arrayref() can then be called again to fetch more rows. This is especially useful when you need the better performance of fetchall_arrayref() but don't have enough memory to fetch and return all the rows in one go. Hope this helps...	[reply] [d/l]
Re: Unexpected Benchmark results with DBI-placeholders by dragonchild (Archbishop) on Jul 21, 2002 at 21:54 UTC
Try using prepare_cached() instead of prepare() and try it again. ------ We are the carpenters and bricklayers of the Information Age. Don't go borrowing trouble. For programmers, this means Worry only about what you need to implement.	[reply]
Re: Re: Unexpected Benchmark results with DBI-placeholders by perrin (Chancellor) on Jul 21, 2002 at 22:21 UTC
That won't make a difference. He is only doing a single call to prepare() in the second version.	[reply]


Don't ask to ask, just ask
	PerlMonks