Sum or Store?

[CygnusX]

Lets say you have a table call Units which contains a list of army units and quantities each player has.   Lets also say that every page displays the total number of units (across all unit types) in your army.  Is it better to:

A) Do a Select * from Units, then do a while loop to sum the total quantity

or

B) Keep a sum on file in the general_user_info table? 

The former is obvious much easier to keep accurate records for.  The latter appears to have some performance advantages.  I'm leaning toward method A, but as I have multiple sets of information that will be treated in this fashion, I thought I'd seek the wisdom of the community before going through a ton of database setup changes.

[Chris]

No brainer here, B.

You just have to call recalculate_units(); each time you change units quantity. But since you will need to call some recalculate_bonus(); after each army change anyway, you can just add one line inside the function, so no additional maintenance overhead at all.

[CygnusX]

Thanks.  DB optimization is still a weak point of mine.  I can understand the concepts well enough, but I don't have a strong coding background. : \

[Chris]

I think you worry about it too early and too much. After the game launch you would be able to switch from A to B if needed... Besides, you could get "lucky" and you game could turn out to be not fun and then you would be able to avoid all the optimization since these few players would not affect the server at all Or the opposite, your game might turn out so extremely fun that you would be forced to hire a team of top best SQL experts to code it and write your own language instead of PHP to make it work

Optimization matters only if you are "in the middle".

[Harkins]

Lets say you have a table call Units which contains a list of army units and quantities each player has.   Lets also say that every page displays the total number of units (across all unit types) in your army.  Is it better to:

A) Do a Select * from Units, then do a while loop to sum the total quantity
B) Keep a sum on file in the general_user_info table? 

C)

Code: [Select]

select unit_type, sum(quantity) from units where owner = $player_id group by unit_type;


[CygnusX]

I had considered method C, but didn't want to use it because if I'm going to pull information from table_units, i may as well do a reset($Result) of the return values and store it in a class variable in case I needed it later. 

But, now that I've said that, perhaps this will take up more memory than I want and not be beneficial... hmmm

@Chris - I think I worry because I don't know what will happen if I don't worry about it first.  Fear of the unknown and all that : ) 

[Chris]

@Chris - I think I worry because I don't know what will happen if I don't worry about it first.

I know what will happen if you don't worry about it first. You will have the game made quick and fast and early

[CygnusX]

 

Perhaps.  I've actually got what I call a 'rolling alpha' going.  Basically I'm adding features, and letting friends start testing them one feature at a time.  If I'm lucky, I could have a full beta working by the end of March.  We shall see though.

[codestryke]

Option D, database trigger, which is what we use for the same exact scenario. When the table detects an insert or update we fire off the trigger and the summary columns in the player table are automatically updated with the new values.

[Mufasa]

Option D, database trigger, which is what we use for the same exact scenario. When the table detects an insert or update we fire off the trigger and the summary columns in the player table are automatically updated with the new values.

Do tell more. I've heard about triggers here and there but haven't seen much on them (admittedly haven't looked hard). I've only recently gotten into the habit of using foreign keys to be lazy

[Chris]

Can triggers be used to solve concurrency problems? Like a player spends gold to build something and at the some time another player attacks the player and steal the gold, meaning the player have negative gold (since it was both spent and stolen). If I added a trigger to set gold to 0 if gold < 0 would it work?
At the moment I'm using the no overflow of UNSIGNED values in MySQL4 to achieve it (in MySQL5 it would overflow and produce a big number).

[CygnusX]

@Chris, That sound viable.

I'm guessing this trigger is a stored procedure in MySql?  I've never written one before...  but it sounds like a great idea.

[Nox]

Trigger is a procedure that is called upon a certain event. It's nice, on the other hand imho there's nothing about it you can't do in your app instead, so it's up to you which place you like to use better. Maybe triggerd would have a little bit better performance

[Chris]

Maybe triggerd would have a little bit better performance

I would say opposite. If there is any update trigger the storage engine has to check for its conditions upon each update of the table. Even if the triggered row is not in a query at the very least it has to compare all rows with some 'trigger is present' table/flag.

[codestryke]

Trigger is a procedure that is called upon a certain event.

Correct and those events are currently insert, update and delete. We use them when we have something that needs to be summed into another table. A perfect example would be the forums we built for our games. Every time there is an insert or delete from the forumposts table (for a new topic or reply) the trigger gets fired and updates the reply count in the forumtopics table. Even if an error occurs in the code along the way my counts stay current as it is controlled at the database level.

Yes you can do the same thing in code and I really don't know what the true performance implications are. I know that we've been using them for over a year now and haven't seen any unusual spikes in database. It has be a bit easier as there is no additional call from PHP to mySQL to run a query, that alone probably accounts for some type of performance gain.

For me I believe a relational database should take care of the data and writing PHP code to manage database relations, to me, is barbaric. I come from a development background where MSSQL and Oracle were used quite heavily when writing software for companies. Once you've experienced what a true database does and should do it's hard to go back writing SQL text in you applications logic

[Mufasa]

After running into a situation today (well it's always been there), that was getting to be too much of a PITA to keep synced with PHP, I finally broke down and read up on triggers.

Doesn't seem like they'd be faster than php mysql_query()'s but definitely seem to be more consistent and "stable". Thanks!

[Mutant]

Maybe triggerd would have a little bit better performance

I would say opposite. If there is any update trigger the storage engine has to check for its conditions upon each update of the table. Even if the triggered row is not in a query at the very least it has to compare all rows with some 'trigger is present' table/flag.

That can be optimised fairly well by the DBMS, so it tends to be fairly fast. Sure, having a trigger is slower than not having a trigger, but that's not really a fair comparison. The real decision is whether you want reading (i.e. computing a value) to be slow, or you want updating (i.e. firing the trigger) to be slow. In the vast majority of cases, updates happen far less often than reads, so it's usually better to optimise the read. The more expensive it gets to calculate the value, the more value you'll get out of a trigger.

FWIW, I use an ORM (an object-mapping application layer around the DB), where I create application level triggers to compute values and store them in a field. For instance, I have a trigger on the 'character_id' field on items, so that when an item is given to a character, or they find an item, etc., it calculates the character's encumbrance, and stores that in the DB. This calculation was pretty expensive, and since you can have several characters in a party, before I put the trigger in every time the party moved, it was having to do that calculation for each character. The small delay when an item changes hands is barely noticeable, but the speed increase in moving around is a huge boost.

Putting triggers in the application is slower than a DB trigger, but I prefer it since it keeps all the code in one place, which makes testing easier. Plus, I find the DB languages tend to get a bit hairy. You need to have your code structured well, though, i.e. only have one way the DB is accessed, which is something an ORM gives you.