Game taking far too long to update database

[cliftonbazaar]

Hi All,

 

Long question but I hope someone will be able to point me in the right direction.

 

At the moment I am writing a cricket management game and and each hour each team is updated with the code

<?php
#Players MUST be updated from this screen so they cna get team bonuses
$teamUpdateTime=$team['teamUpdateTime'];  #This is the time that the team is updated
$teamMorale=$team['morale'];  #Get the teams morale
while($teamUpdateTime<$updateTime) {  #Keep looping through this until we are up to date
echo "<BR>".$team['name'];
	#Work out changes to team morale
	if($teamMorale>1000) $teamMorale -= mt_rand(0, 3);  #If team morale is high then lower it
	if($teamMorale<1000) $teamMorale += mt_rand(0, 5);  #If team morale is low then increase it

	$moraleBonus=0;  #Reset the variable
	$confidenceBonus=0;  #Reset the variable
	$injuryBonus=0;  #Reset the variable
	$energyBonus=0;  #Reset the variable
	$teamUpdateTime += 3600;  #Add an hour to the time

	include("hourly_update_player.php"); #Do players now
}

    #Save the team	
$sqlTeam = "UPDATE teams SET morale='$teamMorale', teamUpdateTime='$teamUpdateTime' WHERE teamID='{$team['teamID']}'";
    if(!mysql_query($sqlTeam, $sqldb)) die('Error: ' . mysql_error());  //Update the record, if there is an error then show it
?>

 

the line include("hourly_update_player.php"); #Do players now then activates the following code

 

<?php
#Playeres will have to be updated with the team because we have to check for team medic
$players = mysql_query("SELECT playerID, name, injuryTime, energy, morale, confidence, fitness FROM players WHERE teamPlayedFor='$team[teamID]'");  #Get all the players of the team
while($player = mysql_fetch_array($players)) {  #Go through all the players of the team
echo "<BR>".$player['name'];

	# Do all the updating of player stats here.  I won't put the full code here as it is not needed for the question on phpfreaks.

	$sqlPlayer = "UPDATE players SET energy='$energy', confidence='$confidence', morale='$morale', injuryTime='$injuryTime' WHERE playerID='{$player[playerID]}'";  #Update player
	if(!mysql_query($sqlPlayer,$sqldb)) die('Error: ' . mysql_error());  //Update the record, if there is an error then show it
}
?>

 

Each team has around 25 players to update.

 

With ten teams (250 players all up) in the game (the game is still pre-alpha) to test it all this takes 8 seconds to go through all the teams and their players.  Unfortunately in this 8 seconds nobody else can use the database as it locks it up.

I tried to have the players update by itself (so it only has to go through the players database once) but cannot work out how to have team affects on the player - for example if the team has a specialist batting coach then the player gets an extra batting point each hour so it needs to go through the teams first.

 

MY QUESTIONS

 

A) How can I speed this up?  When the game goes into the public domain I would like to see around 1-5 thousand teams, this could take up to 800-4,000 seconds - waaaay to long.

 

B) When it is updating the database I can't play the game, it simply says loading until the updates are finished and then continues as normal; with thousands of teams I do expect a bit of delay but how can I keep parts of the database open that aren't being used?

 

C) Each time I go through a team the players database is reloaded, can JOINS fix this? Or is there another way of doing this?

[gristoi]

You have a few problems. A couple of things you can do to speed things up are:

 

a. Index your tables correctly. This will increase the speed dramatically.

b. use a table join in your query. As a matter of principle I always use a join and never ( if i can help it ) use a query within a loop

[cliftonbazaar]

Have taken your advice and learnt JOINS.

 

Now players are not called as part of the teams loop, it is a separate call where all players have a time they are updated.  So now there is only one call to the players database.

 

The code for the players is

<?php
#Playeres will have to be updated with the team because we have to check for team medic
$players = mysql_query("SELECT players.playerID, players.name, players.injuryTime, players.energy, players.morale, players.confidence, players.fitness, players.hourUpdateTime,
	teams.staff
	FROM players
	INNER JOIN teams
	ON players.teamPlayedFor = teams.teamID
	WHERE players.hourUpdateTime<'$updateTime'");  #Get all the players of the team
while($player = mysql_fetch_array($players)) {  #Go through all the players of the team
echo "<BR>".$player['name'];
if($player['staff']) echo " - Hey, I've got a coach!";
	$injuryTime=$player['injuryTime'];  #If a player is injured then this is how long they are still going to be injured for
	$energy=$player['energy'];  #Players energy  
	$morale=$player['morale'];  #Players morale
	$confidence=$player['confidence'];  #Players confidence

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

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

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

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

	$sqlPlayer = "UPDATE players SET energy='$energy', confidence='$confidence', morale='$morale', injuryTime='$injuryTime', hourUpdateTime=hourUpdateTime+3600 WHERE playerID='{$player[playerID]}'";  #Update player
	if(!mysql_query($sqlPlayer,$sqldb)) die('Error: ' . mysql_error());  //Update the record, if there is an error then show it
}
?>

 

but for 160 players (each one is a row = 160 rows) it is taking 8 seconds to go through all of them, is there a way to speed this up?

[gristoi]

There is still something seriously wrong if it takes 8 seconds. I have a few tables that hold around 800,000 records and take less than a second to get data from / update multiple records.

 

You might want to have a look at the EXPLAIN function in mysql. This can help you find why your queries are running so slow. Basically, what you think your recordset is searching through and what it actually doing can be two completely different things.

 

I have had queries that have run slow in the past and by using explain i have found that due to an incorrect query / join instead of searching through 160 rows it is actually searching through 160*160*160 .........ending up with a queries that loops through millions of rows repeatedly.

 

It might not be this but it will be a good starting point. Also have you tried this on your localhost to ensure there is no issue with the mysql server itself.

[cliftonbazaar]

Have gone through your suggestion of EXPLAIN and found nothing wrong, but during testing I found that if I comment out the following lines

$sqlPlayer = "UPDATE players SET energy='$energy', confidence='$confidence', morale='$morale', injuryTime='$injuryTime', hourUpdateTime=hourUpdateTime+3600 WHERE playerID='{$player[playerID]}'";  #Update player
if(!mysql_query($sqlPlayer, $sqldb)) die('Error: ' . mysql_error());  //Update the record, if there is an error then show it

then it takes no time at all (less than 1 second); so how can I speed up the updating of all the records?

[gristoi]

Ok, now you've pinpointed where the issue is . You now need to see what it is trying to insert. Comment out

if(!mysql_query($sqlPlayer, $sqldb)) die('Error: ' . mysql_error());

And replace it with

Echo 'query :'.$sqlPlayer.'<br/>

and check that

A. It is only inserting 160 records

B. The data and user Id is correct

[cliftonbazaar]

Did what you said and it was doing it all correctly, it was updating each record during the loop and still taking 8 seconds.

 

Found another piece of code on the Internet that solved all my problems -

		$query[] ="UPDATE players SET energy='$energy', confidence='$confidence', morale='$morale', injuryTime='$injuryTime', hourUpdateTime=hourUpdateTime+3600 WHERE playerID='{$player[playerID]}'";
}
foreach($query as $id) {
    	mysql_query($query[$id]);
}

This codes takes less than 1 second to update 160 rows.