updating table runs extremely slowly..

[MatthewP]

I'm using MySQL on unix, and querying the database with Perl scripts using DBI. But doing updates to the table runs extremely slowly.. about 150 records an hour!

I am trying to assimilate a pipe delimited text file into the database daily. The script reads each line of the file, looks to see if it can find the stock number in the field, and if it can't it adds the record - if it can it updates the record.

If it's all new items it runs through a few hundred in seconds, but if it has to do the update, each one takes the same time as adding a few hundred records. Consequently I'm trying to update 900 items today and I'm 4 hours in - just over half way through.

So how can I speed up the update function? I've posted the code below in case I'm doing something stupid - but I can't see how else this can be achieved..

Thanks,
Matt.

########### code #################
# this program is called from
# the cron tab - hence no
# content-type as output is not
# required. Same problems are
# experienced from a browser though..

#!/usr/bin/perl
use DBI;
use dbmodule qw ($dbh); # connects to database via the module..
use module(dienice);
$added=0;
$assimilated=0;

#generic update code.
$updateRow = $dbh->prepare("update products set letter = ?,
name = ?, title = ?, format = ?, price = ?, description
= ?, stockno = ?, section = ?, img = ?, weight = ?, date
= ?, label = ?, catno = ?, fixed = ?, cdpack = ?, lppack
= ?, status = ? where stockno = ?&quot or dienice("Can't add
to table: ",$dbh->errmsg);

#read uploaded products file..
$path="/home/myUserName/public_html/cgi/data/products.txt";
open (INF,"$path&quot or dienice("Couldn't open $path for
reading: $! \n"
@allproducts = <INF>;
close(INF);

#foreach line of the file, split into components at |
foreach $line (@allproducts) {
$line =~ s/\&quot;/\&quot;/g;
chop $line;
($letsection,$name,$title,$format,$price,$descript,$stockno,
$section,$img,$weight,$date,$label,$catno,$fixed,$cdpacking,
$lppacking,$status) = split(/\|/,$line);

#try and select product in database..
$products = $dbh->prepare(&quot;select stockno from products
where stockno = \&quot;$stockno\&quot;&quot or dienice(&quot;Can't select all
from products: &quot;,$dbh->errmsg);

$rv = $products->execute;
while ($h = $products->fetchrow_hashref) {
#if this loop of code runs, product is already there, so
update it - this is what takes ages!

$updateRow->execute(&quot;$letsection&quot;,&quot;$name&quot;,&quot;$title&quot;,&quot;$format&quot;,&quot;$price&quot;,&quot;$descrip
t&quot;,&quot;$stockno&quot;,&quot;$section&quot;,&quot;$img&quot;,&quot;$weight&quot;,&quot;$date&quot;,&quot;$label&quot;,&quot;
$catno&quot;,&quot;$fixed&quot;,&quot;$cdpacking&quot;,&quot;$lppacking&quot;,&quot;$status&quot;,&quot;$stock
no&quot;
$assimilated++;
}

if ($rv==0){
#and if this loop runs, product gets added. This bit runs nice and quickly!
$insertRow = $dbh->do(&quot;insert into products values (\&quot;$letsection\&quot;,\&quot;$name\&quot;,\&quot;$title\&quot;,\&quot;$format\&quot;,$price,\&quot;$
descript\&quot;,\&quot;$stockno\&quot;,\&quot;$section\&quot;,\&quot;$img\&quot;,\&quot;$weight\&quot;,\&quot;
$date\&quot;,\&quot;$label\&quot;,\&quot;$catno\&quot;,\&quot;$fixed\&quot;,\&quot;$cdpacking\&quot;,\&quot;$l
ppacking\&quot;,\&quot;$status\&quot&quot or dienice(&quot;Can't add products to
table: &quot;,$dbh->errmsg);
$added++;
}

}#close main loop

$dbh->disconnect;

$mailprog = '/usr/lib/sendmail';

open (MAIL, &quot;|$mailprog -t&quot;
print MAIL &quot;To: user\@mysite.com\n&quot;;
print MAIL &quot;From: webadmin\@mysite.com\n&quot;;
print MAIL &quot;Subject : Database Upload Results\n&quot;;
print MAIL &quot;Database Update Results\n\n&quot;;
print MAIL &quot;Products added : $added\n&quot;;
print MAIL &quot;Products updated : $assimilated\n&quot;;

close(MAIL);
exit;