TalkPHP - View Single Post - mysql -- most efficient way to batch process UPDATES

drewbee · 05-05-2008, 06:06 PM

Hey all. I currently have a program that can be launched by the user of which does processing on their records specific. Part of the process is to select out specific records and make an update to them with the recently pulled ID.

Currently I am running the program to batch update in groups of 25. I will select 25 records that need updated, and once they are all pulled in, update each specific row with its new data.

The problem this poses is that I do not want to try and do this as efficiently as possible (since it is an unknown as to how many users may launch this at anyone time).

Doing a UPDATE query call for each iteration is unacceptable (I think anyways).

and for some reason, mysqli_multi_query took almost twice the amount of time as an acutal individual call to each update.

Does anyone know the basics behind how transactions work? If i loop over the updates within a transaction, then call a commit at the end does this force it to truely be done within 1 call? or does will it still make 25 seperate calls to the database for the update?

Psuedo Code:

PHP Code:


			
{start transaction}
  {foreach $record}
    {UPDATE table set foo = bar WHERE id = $record[id]}
  {/foreach}
{/end transaction; commit}

Is this the best possible way to tackle this?

05-05-2008, 06:06 PM	#1 (permalink)
drewbee The Acquainted Join Date: May 2008 Posts: 175 Thanks: 9	mysql -- most efficient way to batch process UPDATES Hey all. I currently have a program that can be launched by the user of which does processing on their records specific. Part of the process is to select out specific records and make an update to them with the recently pulled ID. Currently I am running the program to batch update in groups of 25. I will select 25 records that need updated, and once they are all pulled in, update each specific row with its new data. The problem this poses is that I do not want to try and do this as efficiently as possible (since it is an unknown as to how many users may launch this at anyone time). Doing a UPDATE query call for each iteration is unacceptable (I think anyways). and for some reason, mysqli_multi_query took almost twice the amount of time as an acutal individual call to each update. Does anyone know the basics behind how transactions work? If i loop over the updates within a transaction, then call a commit at the end does this force it to truely be done within 1 call? or does will it still make 25 seperate calls to the database for the update? Psuedo Code: PHP Code: `{start transaction} {foreach $record} {UPDATE table set foo = bar WHERE id = $record[id]} {/foreach} {/end transaction; commit}` Is this the best possible way to tackle this?