cliftonbazaar Posted August 28, 2011 Share Posted August 28, 2011 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? Quote Link to comment Share on other sites More sharing options...
PFMaBiSmAd Posted August 28, 2011 Share Posted August 28, 2011 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. Quote Link to comment Share on other sites More sharing options...
fenway Posted August 29, 2011 Share Posted August 29, 2011 Show your CREATE TABLE otuput. Quote Link to comment Share on other sites More sharing options...
cliftonbazaar Posted August 30, 2011 Author Share Posted August 30, 2011 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 :'( Quote Link to comment Share on other sites More sharing options...
PFMaBiSmAd Posted August 30, 2011 Share Posted August 30, 2011 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"; Quote Link to comment Share on other sites More sharing options...
mikosiko Posted August 30, 2011 Share Posted August 30, 2011 @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? Quote Link to comment Share on other sites More sharing options...
cliftonbazaar Posted September 1, 2011 Author Share Posted September 1, 2011 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 . Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.