[web] Slow hourly update script when players get more and more
Author
Hi all,
I'm currently managing a small text-based game (via PHP + mysql) and making use of a hourly update script to update the players' resources.
I am experiencing a slow down of script execution with the increasing of players. My hourly update script work as follows:
1. Select all rows from users table.
2. Check every players' bonus row by row, then update the users' resources.
May I know how do most of the game developers handle this? Do you do it in a different way?
How long does it take to update resources for a 1000+ playerbase generally? less than 10 seconds?
Any help / advise will be greatly appreciated.
Thank you.
Can you count how many SQL queries you have to execute for each player (on average)? Executing a single SQL query usually takes somewhere around 0.001 second. So, if you have to execute like 25 SQL queries per player, and you have 1000 players, that's 25000 queries, taking roughly 25 seconds.
I suggest looking into arranging your tables in such a way that you can use either
a) far less SQL queries per player
or
b) update all players with a single SQL query
I suggest looking into arranging your tables in such a way that you can use either
a) far less SQL queries per player
or
b) update all players with a single SQL query
It's been a little while since I did much SQL, but can't you just do something like:
to update the entire table in one block?
John B
UPDATE player_db SET stuff = stuff + bonusto update the entire table in one block?
John B
Author
thanks for the replies :)
I am actually thinking of whether if I can update all players in an UPDATE query. The problem is that every player has different resources bonuses..oh wait...
Now that I think of, will it be faster if I pull out the array of data first, work out every players bonus and save to array, then create a while loop for updating all players?
INSTEAD of
Select all players and with a WHILE loop, calculate every player's bonuses then update one by one.
I just need some ideas thou..as i predict a problem if the playerbase grow larger and larger..
I am actually thinking of whether if I can update all players in an UPDATE query. The problem is that every player has different resources bonuses..oh wait...
Now that I think of, will it be faster if I pull out the array of data first, work out every players bonus and save to array, then create a while loop for updating all players?
INSTEAD of
Select all players and with a WHILE loop, calculate every player's bonuses then update one by one.
I just need some ideas thou..as i predict a problem if the playerbase grow larger and larger..
Put the formula in the query.
UPDATE Players SET IronResources = IronResources + (SomeBonus * 0.035)
UPDATE Players SET IronResources = IronResources + (SomeBonus * 0.035)
I suggest this:
UPDATE players SET stuff = stuff + bonus
All you need to do is fugure out the bonus before hand. I suppose that if nothing changed (like getting or loosing resource-generating items), that the bonus stays the same. So, instead of trying to figure out what the bonus is each hour, simply calculate how big the bonus is on the moment the player gets or looses a resource-generating item. Then save that bonus in the player table. You can then update all the players with a simple SQL query like above or like GroZZleR's.
UPDATE players SET stuff = stuff + bonus
All you need to do is fugure out the bonus before hand. I suppose that if nothing changed (like getting or loosing resource-generating items), that the bonus stays the same. So, instead of trying to figure out what the bonus is each hour, simply calculate how big the bonus is on the moment the player gets or looses a resource-generating item. Then save that bonus in the player table. You can then update all the players with a simple SQL query like above or like GroZZleR's.
Author
<UPDATE players SET stuff = stuff + bonus>
That would be ideal if i can do this..however, the bonus must be determined at the point of updating since it can be changed anytime. For example, a player's hero can have a determining effect on the resources bonus and I have to figure out at that point of time, is the hero around? Or the hero has been killed or left the kingdom.
All in all, I'm mainly going over row by row just to check if they got bonus for resources, update respectively and repeats for the next row (next player)...until all rows are finished.
That would be ideal if i can do this..however, the bonus must be determined at the point of updating since it can be changed anytime. For example, a player's hero can have a determining effect on the resources bonus and I have to figure out at that point of time, is the hero around? Or the hero has been killed or left the kingdom.
All in all, I'm mainly going over row by row just to check if they got bonus for resources, update respectively and repeats for the next row (next player)...until all rows are finished.
There may be a way of doing it to calculate the bonuses on a per-player basis, entirely within the database.
For example, if you were using MySQL, you can do multi-table updates, and use temporary tables.
So you could create a temporary table to store the bonuses, set them all to 0 initially, then do a sequence of updates to add the bonus based on the amount of resources, heroes etc, for all players at once.
Then when you'd finished those queries, update the main player info table to add everyone's bonus, then drop the temporary table (not really required, it will go away by itself).
This would require a fixed number of queries regardless of the number of players.
However, even this would become gradually slower (probably linearly) based on the number of players. Whatever method you use, it will not be any better than linear time, you will have to live with that.
Mark
For example, if you were using MySQL, you can do multi-table updates, and use temporary tables.
So you could create a temporary table to store the bonuses, set them all to 0 initially, then do a sequence of updates to add the bonus based on the amount of resources, heroes etc, for all players at once.
Then when you'd finished those queries, update the main player info table to add everyone's bonus, then drop the temporary table (not really required, it will go away by itself).
This would require a fixed number of queries regardless of the number of players.
However, even this would become gradually slower (probably linearly) based on the number of players. Whatever method you use, it will not be any better than linear time, you will have to live with that.
Mark
Quote:Original post by KCgame
<UPDATE players SET stuff = stuff + bonus>
That would be ideal if i can do this..however, the bonus must be determined at the point of updating since it can be changed anytime. For example, a player's hero can have a determining effect on the resources bonus and I have to figure out at that point of time, is the hero around? Or the hero has been killed or left the kingdom.
Then recalculate the bonus when the hero enters or leaves a kingdom, or when he is killed. Recalculate it everytime that the bonus can change. Then you won't have to do it during the hourly upgrade. Unless you have to do a ridiculous amount of bonus updates per hour, you'll be better off this way.
This topic is closed to new replies.
Advertisement
Popular Topics
Advertisement
