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

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]
      That won't make a difference. He is only doing a single call to prepare() in the second version.
[reply]

Back to Seekers of Perl Wisdom