Page 1 of 1

Looking for feedback on the way I am updating rows

Posted: Tue Mar 24, 2009 9:21 pm
by vbmark
Hello,

As far as performance does it matter which way I do this?

I have an array of ID's (could be around 20-50) that I pass to a function to UPDATE each ROW in a TABLE of that ID.

I am using ADODB and thought it would be best to do something like this:

Code: Select all

$DB->StartTrans();
for ($i = 0; $i < count($theIdList); $i++) {
  $this->database->Execute("UPDATE mytable SET val1 = $i WHERE id = $theIdList[$i]");
}
$DB->CompleteTrans();
However, although it is a simple update it seems to be unreliable. (I am doing this through AJAX, if it matters.)

So, I removed the transaction stuff and just started doing it this way:

Code: Select all

for ($i = 0; $i < count($theIdList); $i++) {
  $this->database->Execute("UPDATE mytable SET val1 = $i WHERE id = $theIdList[$i]");
}
It seems to be more reliable, however, now I fear that I may be slamming my database too much by hitting it with updates in the for loop.

Is this a bad thing to do? Should I be worried about this or is this a normal thing? Or is there a better way?

Thanks!!

Re: Looking for feedback on the way I am updating rows

Posted: Wed Mar 25, 2009 12:26 am
by sujithtomy
Hello,

for 30-50 executions on a loop you don't need to worry about performance, but loop get higher iterations it affects performances,

Stored Procedures are good solution i think.

Regards,
Sujith

Re: Looking for feedback on the way I am updating rows

Posted: Wed Mar 25, 2009 12:39 pm
by vbmark
Can I pass in a PHP array?

I have never done a MySQL stored procedure before. I get the general idea from the link you provided.

How would I loop through an array?

Thanks!

Re: Looking for feedback on the way I am updating rows

Posted: Fri Mar 27, 2009 12:51 am
by sujithtomy
yes you can,

to loop php array
foreach() , for , while() are useful.... :)