Re: How to speed UPDATES

[Christopher Pryce]

On Thursday, April 1, 2004, at 02:16 PM, Etienne Orliac wrote:

>  The piece of code for the update is ():
>
> foreach my $h (@$Selec)
> {
>         my $epoch  = ${%$h}{'EPOCH'};
>         my $site   = ${%$h}{'SITE'};
>         my $corsite  =  ("'"."$site"."'");    # Corresponds to the 
> station field
>         my $corep    = ("'"."$epoch"."'");  # Corresponds to the epoch 
> field

consider calling $dbh->quote() or use placeholders

>         my $sth = $dbh->do( "
>                               UPDATE $table
>                               SET $SET
>                               WHERE ( epoch LIKE $corep  AND station = 
> $corsite)
>                               LIMIT 1
>                              ");
> }

Ideally, you should prepare a statement outside of a foreach loop, and 
execute it inside, something along the lines of  (untested and normal 
error checking omitted):

my $sql = qq(  UPDATE $table SET $SET WHERE ( epoch = ? AND station = ? 
) );
my $sth = $dbh->prepare( $sql );

foreach my $h (@$Selec) {
	$sth->execute( $h->{'EPOCH'}, $h->{'SITE'} ) ;
}

Note that using placeholders eliminates the need for quoting char 
values. The Driver does that for you. For a batch of 1500 inserts, you 
eliminate 1500 prepare statements, which will be a great savings.

cp