At 17:00 +0000 3/8/02, Corin Hartland-Swann wrote:
>Hi there,
>
>I have a medium-size table (3 million rows) with several pieces of data
>including an ID and a number (called id and num below). I have a program
>which builds a hash in perl from a data file of the 'new' numbers for each
>id, only some of which have changed (5-10%).
>
>To update the table I tried using the following code (paraphrased):
>
>--------------------------------------------------------------------------
>
>$sth = $dbh->prepare("SELECT id,num FROM table", { 'mysql_use_result' => 1 });
>
>$sth->execute;
>
>$sth->bind_columns(\$id, \$num);
>
>while ($sth->fetch)
>{
> $new_num = $hash{$id};
>
> $dbh->do("UPDATE table SET num = $new_num WHERE id = $id")
> if $num != $new_num;
>}
>
>$sth->finish;
>
>--------------------------------------------------------------------------
>
>This works for a varying number of rows (usually 5,000 to 10,000) before
>it silently exits the loop. I'm using MySQL 3.23.49a
>
>I was wondering if there is a problem with doing this in MySQL. Because of
>the size of the table and my script's already gargantuan memory footprint
>(it tops out 600 MB by the time it gets to this stage) I need to avoid
>storing the entire result in DBI, hence mysql_use_result.
That's also your problem. With mysql_use_result, it's *required*
that you completely finish the query before issuing another one.
I suppose you could write the IDs to a file, then read them back in
and use them to issue the UPDATE statements.
>
>As an alternative I could mark all the id's that have changed and then
>perform the updates after I have closed the table, but I expected this way
>of doing it to work fine. I have run it through numerous times, and it
>always drops out of the loop at the same sort of interval (but often
>differs from run to run).
>
>Should this be working or should I find another way? If it is a bug then I
>will of course try to produce more information, but I just want to check
>I'm doing the right thing first.
>
>Many Thanks,
>
>Corin
>
>/------------------------+-------------------------------------\
>| Corin Hartland-Swann | Tel: +44 (0) 20 7491 2000 |
>| Commerce Internet Ltd | Fax: +44 (0) 20 7491 2010 |
>| 22 Cavendish Buildings | Mobile: +44 (0) 79 5854 0027 |
>| Gilbert Street | |
>| Mayfair | Web: http://www.commerce.uk.net/ |
>| London W1K 5HJ | E-Mail: [EMAIL PROTECTED] |
>\------------------------+-------------------------------------/
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
