# [web] Slow hourly update script when players get more and more

This topic is 4661 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

## Recommended Posts

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.

##### Share on other sites
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

##### Share on other sites
It's been a little while since I did much SQL, but can't you just do something like:
UPDATE player_db SET stuff = stuff + bonus

to update the entire table in one block?

John B

##### Share on other sites
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?

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..

##### Share on other sites
Put the formula in the query.

UPDATE Players SET IronResources = IronResources + (SomeBonus * 0.035)

##### Share on other sites
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.

##### Share on other sites
<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.

##### Share on other sites
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

##### Share on other sites
Quote:
 Original post by KCgameThat 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.

1. 1
2. 2
3. 3
4. 4
Rutin
19
5. 5

• 13
• 14
• 9
• 9
• 9
• ### Forum Statistics

• Total Topics
632927
• Total Posts
3009249
• ### Who's Online (See full list)

There are no registered users currently online

×