Speeding up multiple UPDATES

[cliftonbazaar]

I have a game that updates every 5 seconds and does multiple UPDATES; echoing the code gives me

UPDATE players SET energy='1000', confidence='1002', morale='1000', injuryTime='0', hourUpdateTime=hourUpdateTime+3600 WHERE playerID='1'
UPDATE players SET energy='1000', confidence='1001', morale='998', injuryTime='0', hourUpdateTime=hourUpdateTime+3600 WHERE playerID='2'
UPDATE players SET energy='1000', confidence='999', morale='1000', injuryTime='0', hourUpdateTime=hourUpdateTime+3600 WHERE playerID='3'
UPDATE players SET energy='1000', confidence='1002', morale='1000', injuryTime='0', hourUpdateTime=hourUpdateTime+3600 WHERE playerID='4'
UPDATE players SET energy='1000', confidence='999', morale='999', injuryTime='0', hourUpdateTime=hourUpdateTime+3600 WHERE playerID='5'

 

At the moment I have 282 players in the database which is taking 9 seconds, is there a way to speed this up?

[PFMaBiSmAd]

Are you sure the problem is the UPDATE queries and not the processing you are doing to produce the values?

 

How are the UPDATE queries being formed? If you are first selecting the data, then UPDATING it, that is generally not necessary, depending on what you are doing.

 

If you want help with a performance problem with your code, you would need to post all the code being executed so that someone could actually see what you are doing and where the possible problems are at. It may very well be that for the number of players, you are already getting the highest throughput and the solution would be to limit or break up the players between multiple servers.

 

Edit: About the only general thing that comes to mind that would speed up executing a number of same queries, with different data values, would be to use prepared queries.

[fenway]

Show your CREATE TABLE otuput.

[cliftonbazaar]

The PHP code is

echo "<BR>Start Time ".time();	
if($playerQuery) {
	foreach($playerQuery as $query) {
echo "<BR> ".$query;
		if(!mysql_query($query, $sqldb)) die('Error: ' . mysql_error());  //Update the record, if there is an error then show it
	}
	$playerQuery=NULL;  #Null the value so it doesn't happen again
}
echo "<BR>End Time ".time();

which gives the output of

Start Time 1314699571

UPDATE players SET energy='895', confidence='815', morale='1001', injuryTime='0', hourUpdateTime='1314700659' WHERE playerID='253'

UPDATE players SET energy='1000', confidence='817', morale='1002', injuryTime='0', hourUpdateTime='1314700659' WHERE playerID='254'

UPDATE players SET energy='924', confidence='818', morale='1000', injuryTime='0', hourUpdateTime='1314700659' WHERE playerID='255'

UPDATE players SET energy='783', confidence='815', morale='1000', injuryTime='0', hourUpdateTime='1314700659' WHERE playerID='256'

UPDATE players SET energy='898', confidence='815', morale='1000', injuryTime='0', hourUpdateTime='1314700659' WHERE playerID='257'

UPDATE players SET energy='745', confidence='816', morale='999', injuryTime='0', hourUpdateTime='1314700659' WHERE playerID='258'

UPDATE players SET energy='1000', confidence='813', morale='997', injuryTime='0', hourUpdateTime='1314700659' WHERE playerID='259'

UPDATE players SET energy='917', confidence='815', morale='1000', injuryTime='0', hourUpdateTime='1314700659' WHERE playerID='260'

UPDATE players SET energy='763', confidence='817', morale='1005', injuryTime='0', hourUpdateTime='1314700659' WHERE playerID='261'

UPDATE players SET energy='810', confidence='819', morale='1001', injuryTime='0', hourUpdateTime='1314700659' WHERE playerID='262'

UPDATE players SET energy='873', confidence='816', morale='1005', injuryTime='0', hourUpdateTime='1314700659' WHERE playerID='263'

UPDATE players SET energy='806', confidence='818', morale='1005', injuryTime='0', hourUpdateTime='1314700659' WHERE playerID='264'

UPDATE players SET energy='1000', confidence='816', morale='1002', injuryTime='0', hourUpdateTime='1314700659' WHERE playerID='265'

UPDATE players SET energy='803', confidence='813', morale='999', injuryTime='0', hourUpdateTime='1314700659' WHERE playerID='266'

UPDATE players SET energy='756', confidence='816', morale='1002', injuryTime='0', hourUpdateTime='1314700659' WHERE playerID='267'

UPDATE players SET energy='905', confidence='809', morale='1000', injuryTime='0', hourUpdateTime='1314700659' WHERE playerID='268'

UPDATE players SET energy='995', confidence='820', morale='999', injuryTime='0', hourUpdateTime='1314700659' WHERE playerID='269'

UPDATE players SET energy='800', confidence='818', morale='1002', injuryTime='0', hourUpdateTime='1314700659' WHERE playerID='270'

UPDATE players SET energy='817', confidence='815', morale='1001', injuryTime='0', hourUpdateTime='1314700659' WHERE playerID='271'

UPDATE players SET energy='834', confidence='817', morale='1002', injuryTime='0', hourUpdateTime='1314700659' WHERE playerID='272'

UPDATE players SET energy='757', confidence='811', morale='1000', injuryTime='0', hourUpdateTime='1314700659' WHERE playerID='273'

UPDATE players SET energy='782', confidence='819', morale='1001', injuryTime='0', hourUpdateTime='1314700659' WHERE playerID='274'

UPDATE players SET energy='743', confidence='810', morale='1003', injuryTime='0', hourUpdateTime='1314700659' WHERE playerID='275'

UPDATE players SET energy='763', confidence='816', morale='1000', injuryTime='0', hourUpdateTime='1314700659' WHERE playerID='276'

UPDATE players SET energy='733', confidence='812', morale='1001', injuryTime='0', hourUpdateTime='1314700659' WHERE playerID='277'

UPDATE players SET energy='913', confidence='816', morale='1002', injuryTime='0', hourUpdateTime='1314700659' WHERE playerID='278'

UPDATE players SET energy='803', confidence='816', morale='1005', injuryTime='0', hourUpdateTime='1314700659' WHERE playerID='279'

UPDATE players SET energy='829', confidence='810', morale='1000', injuryTime='0', hourUpdateTime='1314700659' WHERE playerID='280'

UPDATE players SET energy='1000', confidence='817', morale='1002', injuryTime='0', hourUpdateTime='1314700659' WHERE playerID='281'

UPDATE players SET energy='900', confidence='812', morale='1000', injuryTime='0', hourUpdateTime='1314700659' WHERE playerID='282'

End Time 1314699573

Updated 30 players.

 

This takes 2 seconds which is way too long  :'(

[PFMaBiSmAd]

Since you didn't answer the questions or post the code we asked for, I looked back through your threads and found some likely code to test with.

 

Using an UPDATE query for each player, in a loop, I got similar results. ~3.7 seconds for 200 rows (with the update query commented out, it took ~30ms.) I also tried a mysqli prepared statement for the UPDATE query, with little or no speed difference (at most it was .1 second faster.) I also tried the UPDATE query with and without the LOW_PRIORITY keyword (no measurable speed difference.) Then I tried a mysqli prepared REPLACE query in a loop (no measurable speed difference.) I suspect that most of the time taken by this is for the communications between php/mysql for each query or in the case of the prepared query, the time taken to communicate the values to put into the prepared query (I am using the latest php5.3 with the mysqlnd driver on a Windows development system.)

 

Lastly, I tried a multi-value replace query - REPLACE table_name (columns) VALUES (row data),(row data),(row data),.... This took ~50ms for the 200 rows. This requires that you retrieve all the column values from the existing rows and put them back into the replacement row data. You will also need to calculate the hourUpdateTime in your script instead of in the query.

 

This is the code I tested with -

<?php
$st = microtime(true);

#Players will have to be updated with the team because we have to check for team medic
$query = "SELECT players.playerID, players.name, players.injuryTime, players.energy, players.morale, players.confidence, players.fitness, players.hourUpdateTime, players.teamPlayedFor,
	teams.staff
	FROM players
	INNER JOIN teams
	ON players.teamPlayedFor = teams.teamID
	WHERE players.hourUpdateTime<'$updateTime'";  #Get all the players of the team
$players = mysql_query($query);

if(!mysql_num_rows($players)){
echo "No matching rows";
} else {
while($player = mysql_fetch_assoc($players)) {  #Go through all the players of the team
	extract($player,EXTR_PREFIX_ALL,'r'); // $r_playerID, $r_name, $r_injuryTime,...
	echo $r_name;
	if($r_staff) echo " - Hey, I've got a coach!";
	echo "<br />";

	#Energy
	if($r_energy<1000) $r_energy += mt_rand(1, $r_fitness/100+$energyBonus);  #Get some energy back
	if($r_energy>1000) $r_energy = 1000;  #Don't allow energy to go over 1000

	#Morale
	if($r_morale<1000) {$r_morale += mt_rand(1, $r_confidence/100+$moraleBonus);  #Get some morale back if it is low
	} else {$r_morale -= mt_rand(0, 3);}  #If morale is high then lose a little

	#Confidence
	if($r_confidence<1000) {$r_confidence += mt_rand(0, 3+$confidenceBonus);  #Get some confidence back if low
	} else {$r_confidence -= mt_rand(0, 2);}  #If confidence is high then lose a little

	#Injuries
	if($r_injuryTime) {
		$r_injuryTime--;  #Take a bit of injury time off
		$r_injuryTime -= $injuryBonus;  #Bonus to healing
		if($r_injuryTime<0) $r_injuryTime=0;  #Can't have a negative number
	}

	$r_hourUpdateTime += 3600;

	// produce 9 replacement values for each query
	$data[] = array($r_playerID,"'$r_name'",$r_injuryTime,$r_energy,$r_morale,$r_confidence,$r_fitness,$r_hourUpdateTime,$r_teamPlayedFor);

	} // end of while loop
//echo '<pre>',print_r($data,true),'</pre>';
$terms = '';
foreach($data as $arr){
	$terms .= "(" . implode(",",$arr) . "),";
}
$terms = rtrim($terms,',');
$query = "REPLACE players (playerID,name,injuryTime,energy,morale,confidence,fitness,hourUpdateTime,teamPlayedFor) VALUES $terms";
//echo "---$query---";
if(!mysql_query($query)){
	echo $mysql_error();
} else {
	echo "done";
}
}
$et = microtime(true);
$time = $et-$st;
echo "Time; $time";

[mikosiko]

@PFM:  Just curious to know why you chose REPLACE instead of the original UPDATE... is not that a call for eventual problems? :

 

- REPLACE is supposed to works like an INSERT but deleting rows if it find duplicates on PK/UK's,  and this behavior can trigger ON DELETE CASCADE constraints (if used) causing non desired effects ... or i'm wrong? 

[cliftonbazaar]

Wow, didn't even think about REPLACE, after spending some time doing minor tweaks to the code you tested with I have got the whole database down from 104 seconds down 2.4 seconds- more than adequate

 

Thanks very much for your help .