Re: Efficiency question

[Christopher R. Jones]

I use the following:
   my ($key, $value);
my $update_line="";

>   while (($key, $value) = each(%hash)) {                # walk through the
>hash
>  $update_line .= "$key=";
   $update_line .= "'$value,' ";
}
my $stmt = "update main set $update_line where entry_number =
\'$entry_number\'";
$dbh->do($stmt) || die "Can't do $stmt: $!\n $dbh->errstr\n";

So you are only doing 1 call to the database, presumably much faster?

>my $stmt = "update main set $key = \'$value\' where entry_number =
>\'$entry_number\'";
>     $dbh->do($stmt) || die "Can't do $stmt: $!\n $dbh->errstr\n";
>   }


>I've read and heard a lot about how efficient using placeholders ( '?',
>etc.) can be in SQL statements rather than doing another $sth for each
>change.
>
>Here is some code I currently use:
>
>sub updatedb {
>   my $hashref = shift;          # take in the hashref from the call
>   my %hash = %$hashref; # de-reference the hash ref
>   my ($key, $value);
>   while (($key, $value) = each(%hash)) {                # walk through the
>hash
>     my $stmt = "update main set $key = \'$value\' where entry_number =
>\'$entry_number\'";
>     $dbh->do($stmt) || die "Can't do $stmt: $!\n $dbh->errstr\n";
>   }
>}
>
>The result will be that since the table has about 78 fields, the $dbh->do is
>called 78 times.  Can anyone tell me if I should change this to "update main
>set ? = \'?\' where . . . " and use $sth->execute($key, $hash{$key}) within
>a foreach $key (keys(%hash)) ?  What kind of speed increase should I see on
>something like this?
>
>In all honesty, the script works pretty fast as it is.  I've had no
>complaints.  I just don't like to be inefficient!
>
>Jonathon M. Robison
>EMS Web/Database Administrator
>VEMA, Office # E116
>Visteon, Inc.
>
>
>
>--
>---------------------------------------------------------------------
>Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
>posting. To request this thread, e-mail mysql-thread37685@stripped
>
>To unsubscribe, send a message to:
>     <mysql-unsubscribe-cj=interlog.com@stripped>

Christopher R. Jones, P.Eng.
14 Oneida Avenue
Toronto, Ontario M5J 2E3
Tel. [phone]
Fax. [phone]
Email cj@stripped