I'm writing a web application for a client in PHP. One function that a user can
perform results in a very expensive update. To try and speed up this process (free up
the user) i wrote a Perl script that performs the bulk of the work. When the user
runs the PHP page, the PHP page updates one field in one record than spawns the Perl
script.
It seamed to be working on my W2K PC so i uploaded it to the server (FreeBSD) to test
run it. To make sure the Perl script didn't suck up all the resources i put in a
sleep(5)... BIG MISTAKE !
It had 400 records to update with a 5 second delay between each update. 400*5sec =
33min !! The site was unavailable for over half an hour !!!
MY QUESTION:
This is my Perl script. I though that adding $query->finish would unlock the table
but it didn't. It looks like the only way around this is to connect/disconnect from
the DB between each update. All that connecting/disconnecting i think would be
expensive in itself. THERE MUST BE A BETTER WAY ! Thoughts, comments please!
=====================================
&openDatabase;
$sql = "select * from answers where userid = $input{\"userid\"}";
$query = $database->prepare($sql);
$query->execute;
#loadData takes the above result set and loads it into an array such as @dbAnswer
&loadData();
$query->finish;
for ($x=0;$x<=$#dbAnswer;$x++) {
# sleep(5);
$sql2 = "update answers set answersuper = \"\", backuprating = $dbAnswer[$x],
backupna = $dbAnswerNA[$x] where userid = $input{\"userid\"} and questionid =
$dbQuestionID[$x]";
$query2 = $database->prepare($sql2);
$query2->execute;
$query2->finish;
}
&closeDatabase;
---------------------------------------------------------------------
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
