Looking for feedback on the way I am updating rows

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
vbmark
Forum Newbie
Posts: 19
Joined: Sun Feb 15, 2009 8:53 pm

Looking for feedback on the way I am updating rows

Post 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!!
sujithtomy
Forum Commoner
Posts: 46
Joined: Tue Mar 24, 2009 4:43 am

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

Post 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
vbmark
Forum Newbie
Posts: 19
Joined: Sun Feb 15, 2009 8:53 pm

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

Post 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!
sujithtomy
Forum Commoner
Posts: 46
Joined: Tue Mar 24, 2009 4:43 am

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

Post by sujithtomy »

yes you can,

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