I've been looking at the efficiency of my game code (written in PHP, with MySQL database). One of the problems is that the battle system hits the database with a bunch of "little" queries to get units involved in an attack, update hits when damage is taken, add or remove effects when spells are used, and so forth. This causes quite a load on the database during a large battle. I am certain that I could write a class that encapsulates all the unit data, fetching it once at the beginning of the battle, modifying the in-memory data as results are updated, and then writing changes to the database at the end of the turn. The table structure is like this:

unit - basic data such as number, hits, movement
  unit_equip - link unit and equip
  unit_effect - link unit and effect, value, duration
  unit_spell - unit spell counts by level

My thought is to build a class that loads the unit data, and data from the sub-tables, on demand. The class would be used to modify values as the turn progresses, and finally to write the changed rows back to the database. I don't have a lot of experience with PHP, so I'm hoping for some helpful advice from others with experience with classes and database abstraction in how to best handle this. I think that even a simple brute-force approach using a "dirty" flag will improve performance, but there may be more subtle ways to code this. Any thoughts on how to proceed with my class design?

In order to make any recommendations, we'd have to see exactly how much you're hitting the database in the code. I've always tried to steer away from writing a class specifically for database interaction, because it seems kind of confusing and pointless to pass everything through a database layer, and THEN pass it on to wherever it needs to go.

What I tend to do instead is organize everything in the database into different categories, and hierarchically load everything in to memory. For example, I'm writing a multiplayer &#106avascript RTS game with a PHP server. RTS games have abilities/powers/magic spells/whatever you want to call it. Abilities are a good example of hierarchical database loading, because there are a lot of things involved. I organized my abilities to have different stages. Each stage has a certain duration, and applies certain effects to various entities, and certain animations that are played in different places. I load all this data into memory on the server at the start of the game, and after that I am done interacting with the database.

To load it in, I would do something like:



Then in the "ability" constructor:



Etc. etc. etc. The same sort of thing happens in the stage class with the ability modifiers and fx.


Since you're writing presumably a turn-based game, it doesn't make sense to load the data needed into memory for each turn. I would almost recommend having a single script that runs on the server perpetually that handles all the turns/combats for all players. That way, you could keep all the data loaded into memory.

Of course there are downsides to doing that. If the script crashes, obviously NO ONE's battles get resolved, rather than just the battle of the person that crashed your script. You'd have to manually restart it. Another downside would be that you would be wasting a non-trivial amount of memory/CPU time if there aren't a lot of battles going on, and you use the computer for other things.

Sorry this post was a little rambly, I hope it was helpful anyway. It's hard to give any specific advice without knowing more details of the code you're trying to optimize.

That is helpful, even though you're not suggesting a class. The multiple or nested array approach is very similar to what I'm doing now, but not very effectively. Everything is loaded for a combat, and then saved, and the same units and data are likely to be loaded and saved several times during the turn, especially if they are heavily engaged.

To clarify the way the DB is hit, I immediately update hits after a single combat, and update effects if they change, and because of all the updates, have to requery everything for units that are involved in a second action, or anything else. So I have dozens of tiny queries to get hits or armor repeatedly.

Here is a little chunk of code that is called after a single combat. Similar code might run for a single unit five or ten times during a turn:

/** * For each attacker, assign the action results (thrown or missile) */function attacker_update($battle_id,$player_id,$clock, &$attacker,$act_txt) {global $db;    // For each attacker that did something    $att_count = count($attacker);    $id_array = array();    for ($a = 0; $a < $att_count; $a++) {        $delay = max(1, $attacker[$a]['clock']);        if ($attacker[$a]['action']) {            $attacker[$a]['stamina'] = max(0, $attacker[$a]['stamina'] - 2);            $sql = "update ao_unit set " .                    "stamina = {$attacker[$a]['stamina']}, " .                    "tactical_state = tactical_state & ~".(STATE_CONCEAL | STATE_CHARGE).", " .                    "tactical_clock = tactical_clock + $delay " .                "where unit_id = {$attacker[$a]['unit_id']}";            $db->query($sql);            // Time passes            $id_list = $attacker[$a]['unit_id'];            time_passes($id_list, $delay);        }    }}

So, I'm looking for a way to encapsulate a way of loading everything, changing numbers and adding/removing records as needed (in memory), then finally, after everything is resolved for the turn, writing the changes to the DB. I should be able to reduce round-trips to the DB server to a fraction of what they currently are, if I do it right.

I don't think it will work well for my game, but I'm curious: How do you have a PHP script run perpetually, while dealing with the max execution time for scripts enforced by the server?

It's still kind of hard to recommend anything without knowing some of the battle requirements/how you gather input from the user for each turn. If the user only inputs once per turn, then the battle is executed, then you're definitely hitting the database too many times.

From what I can tell from the code so far, it seems like you're using some sort of enum to store the attacker data. Rather than classifying everything as an attacker, I would write a class called "unit" or "entity", and have "attacking" be a possible state of an entity.

It's usually possible to disable the maximum execution time for PHP scripts, but the method for doing so varies based on what server you're using. Some have a setting in the php.ini file. In mine (xampp apache) there is a function you can call at the start of your script to set the maximum execution time to whatever you want (or 0, to disable it). It's generally inadvisable to disable the maximum execution time, because if you have an infinite loop bug somewhere in your code, it could very easily cripple your server. Of course, with an RTS game, there's really no choice :/ It just means being very careful with your loops/recursions, making sure they will terminate eventually, no matter what input it's given.

Quote:Original post by Wan

• Is performance really a problem at this moment? Obviously the less table queries and mutations the better, but these days database engines are very well optimized to handle a big load. Can you somehow analyze where potential bottlenecks arise when you put the system under a lot of stress?

Yes, performance is an issue. The action script, where battles are processed, consumes about 40% of the total load on the server. Everything else is the remainder.

Quote:

Is it possible to group commands to the limit the interaction with the database? For selection queries this is obvious, but could you do it for update commands as well? Something like:

UPDATE ao_unit SET stamina = MAX(0, stamina - 5) WHERE unit_id IN (1, 4, 8, 17)

My idea is to group updates. I'm trying to change from a few units to all of them in one batch.

Quote:

Does MySQL have something like MSSQL's stored procedures? A database can compile and cache these operations for higher performance.

If so, does MySQL support procedures with multiple commands, so that you can update and return the results in one call? Something like:

UPDATE ao_unit SET stamina = MAX(0, stamina - 5) WHERE unit_id IN (1, 4, 8, 17)SELECT stamina FROM ao_unit WHERE unit_id IN (1, 4, 8, 17) ORDER BY unit_id

I wish! Not in MySQL ver. 4.x, so I'm stuck with ad-hoc queries.

Quote:

You can use a scheduled task (Windows) or cron job (Linux) to automatically execute a PHP script at intervals. You could use these to batch process attack commands (perhaps stored in a delayed attack table) or clean up the data (delete killed units from the unit table for instance).

Something I don't know: how safe is it to keep states for several turns in your class hierarchy before committing them to the database? Since every user has his own instance of the PHP scripts running (instead of application wide state such as the data in the database; ignoring sessions at the moment), is there a danger that a user will ne shown incorrect data because changes by another user (by attacking for instance) have not been committed to the database yet? And if that's a potential problem, do database operations in general need to be transaction safe?

Deferring some updates might help the user experience, especially the deletes, but my goal is to reduce the load, not spread it out differently.

My hosting company has not upgraded to 5.x. When they do, I'll be recoding a lot of things.

I've posted a design and some snippets of in-development code HERE, if anyone is interested or wishes to comment further.