[guide] Using arrays to store data

[saljutin]

In this article I will try to explain how to write static data into arrays instead of database tables. Doing that you will not need additional queries to select weapons,units, buildings description and/or statistics.

Create file: weapons.php

Code:

<?
//WEAPONS -NAME / MIN DMG / MAX DMG
$weapon[1] = array("Spear",10,15);
$weapon[2] = array("Axe",15,20);
$weapon[3] = array("Hammer",20,25);

//....
?>

this file will store our weapon information, you can add more things to array like which building or technology is needed to produce them or every other thing you need.

Create file: produce.php

Code:

<?
//we include file with weapons
include("weapons.php");

//example is to show list of all weapons possible with attack rates
for ($j=1; $j<=3; $j++) {
echo $weapon[$j][0]." (".$weapon[$j][1]." - ".$weapon[$j][2].") <br>";
}
?>

$weapon[1][0] would mean the first weapon and first position in array $weapon[1] which is name, [1] would mean min dmg, [2] would mean max dmg in our case.

Hope this explains such system, it is useful when you have lots of buildings, technologies and things to produce so with proper planning you can create really great and fast system.

Feel free to comment or ask anything.

[Zeggy]

Some alternate styles, slightly easier to read:

Code:

$weapon['Sword']['dmg'] = 5;
$weapon['Sword']['cost'] = 10;

$weapon['Axe']['dmg'] = 10;
$weapon['Axe']['cost'] = 20;

Code:

$weapon[] = array(  'name'=>'Sword',
                    'dmg'=>5,
                    'cost'=>10
                 );
$weapon[] = array(  'name'=>'Axe',
                    'dmg'=>10,
                    'cost'=>20
                 );

(Leaving the first key of the array empty will have it auto-generated, so you don't need to fill in numbers if you don't want to!)


Storing info using arrays is vey interesting, I tried it once

[codestryke]

In this article I will try to explain how to write static data into arrays instead of database tables. Doing that you will not need additional queries to select weapons,units, buildings description and/or statistics.

If you have a player table and that player table references weapons this is exactly the type of architecture NOT to use. Lets say you wanted to list all player's and what weapon they have. Using the system above you would have to do a query then process each row so you could get what weapon they own. Later you might want to allow player's to have two weapons, what do you do then? If you say oh I'll just have weapon1 field and weapon2 field then you need to really buy a book on database design.

MySQL server is fast, very very fast when doing queries. Is it faster then pulling from memory, no, it is not. However when you mix the two with something like weapons you are actually going slower.

No offense to anyone but this is the perfect example of a  programmers mentality to databases. A databases's strength is the ability to read / write in batches, not process things line by line like in programming. You are working with sets of data, always remember this.

[saljutin]

what is faster
1) simple query which select only IDs of weapons and then use those IDs to get information of weapons from array
2) join, union or whatever queries

I dont think you are aware of all possibilities and how to use arrays...

[codestryke]

Oh I am very much aware of how to use an array to it's fullest benefit and better yet I know when to use them and when not to

If you wanted to just list all the weapons and there id's then yes processing the array would be faster. When you merge (like I said above) the two is when you slow the process down. And as stated above if you want your game to expand in the future you are horribly stuck. But lets work with some examples

Your way

Code:

$rs = $adodb->("SELECT username, weaponid FROM players ORDER BY username");
while( $row = $rs->FetchRow() ) {
  echo $row['username'], " has weapon ", $weapons[$row['weaponid']]['name'], "<br />";
}

The correct way

Code:

$rs = $adodb->("SELECT username, weaponlu.name FROM players, weaponlu WHERE players.weaponid = weaponlu.name ORDER BY username");
while( $row = $rs->FetchRow() ) {
  echo $row['username'], " has weapon ",$row['name'], "<br />";
}

Betcha the correct way is faster And if you wanted to get even more slickery and possibly even faster

Code:

$rs = $adodb->("SELECT CONCAT(username, ' has weapon ', weaponlu.name, '<br />') as lineout FROM players, weaponlu WHERE players.weaponid = weaponlu.name ORDER BY username");
while( $row = $rs->FetchRow() ) {
  echo $row['lineout'];
}

Not only is it a bit faster it's also a LOT easier to understand at a glance.

[codestryke]

Lets add some more scenerios as well...

If you were working in lets say ASP your way could be very useful and possibly faster since ISS/ASP allow you to create global variables across the application server (ie you put something in memory at it's available to all pages all the time as the server itself holds them as long as the server is loaded). With PHP you have globals but they are created and destroyed on every page request.

So in a game you would have possibly 3/4 pages that actually need to reference that weapons array. May be more maybe less, hard to say but the majority of the game would not need them. So lets say you have a message center for in game emails between players. On each of those pages PHP needs to allocate and fill the memory space with the weapons array. Now you've slowed down the entire game with an array that really only needs to be used on 3 or 4 pages.

As your membership increases all of a sudden your server is now running out of memory, or starts to slow down. Course this would need to be a LOT of users but hey isn't that what we all want the end goal to be?


Your Challenge:
Lets say you created some type of gang system in your game. In the gang portal pages you want to give the leader a tactical report which allows the leader to display a report on which members have which weapons and then sort those on attack strength. Please show us the code you will use to display such a report

[saljutin]

OK i made some tests. I selected all units from table units (14 entries), there every unit have building and technology that needs to be built/researched to be able to build units. Buildings table has 22 entries and technology has 30.

normal SELECT: ~0.0015 seconds
using CONCAT: ~0.0015 seconds
using ARRAYS: ~0.0025 seconds

In this case arrays are slower, if I use them in timing process, if I exclude them then they are ~0.0010 seconds. But this proves your theory is correct and I can see my mistake. I always start timing process after I include all files so it excludes writting data into memory.
You are correct and I am not. Happy

[codestryke]

I'm not happy that I'm correct... I am happy though if you learned something.

This is suppose to be a learning site where people come to learn how to make games. If you post code then it's up for a peer review. During my programming career I've had to endure many, many code reviews. At one place I worked we had a sign above the door, "Check your ego here". I saw many of programmers quit after review, on a couple of harsh ones I too had that thought. Then once my ego soothed (or I tested it myself and found I was wrong) I realized, hey I'm a better programmer now It's all a learning process and when anyone doesn't want to learn anymore then it's time to stop programming.

[Chris]

If this was C then arrays would won. We keep forgetting PHP is an interpreted language (at least I keep forgetting ) where ever line of code is very slow no matter how we write it (maybe Zend Optimizer or IonCube would speed it up, but if I remember correcly these cost $1000, so it is out of the question for small/mid size games anyway).

OK i made some tests. I selected all units from table units (14 entries), there every unit have building and technology that needs to be built/researched to be able to build units. Buildings table has 22 entries and technology has 30.

Can you redo the test with 100,000 building table and 10,000 technology table entries? (only the database one, since array would be overkill for sure) I'm interested how much the amount of table entries slow down everything.

[saljutin]

Bah I am a bit lazy to do that
but I can tell you that some optimized map (with 1001x1001 entries in table) takes ~0.040 seconds to load using this

Code:

SELECT m.id,m.owner,m.city,m.slikca,u.username,c.name FROM map m LEFT JOIN users u ON u.id = m.owner LEFT JOIN city c ON c.user_id = m.owner WHERE m.id BETWEEN $id1s AND $id1e OR m.id BETWEEN $id2s AND $id2e OR m.id BETWEEN $id3s AND $id3e OR m.id BETWEEN $id4s AND $id4e OR m.id BETWEEN $id5s AND $id5e OR m.id BETWEEN $id6s AND $id6e OR m.id BETWEEN $id7s AND $id7e OR m.id BETWEEN $id8s AND $id8e OR m.id BETWEEN $id9s AND $id9e OR m.id BETWEEN $id10s AND $id10e OR m.id BETWEEN $id11s AND $id11e

[RangerSheck]

Some thoughts on the topic (because I've considered doing something similar to this in my game):

(a) Testing the speed of SQL queries can be tricky when there's no real load because MySQL caches queries. For example:

Code:

mysql> select count(*) from mobiles;
+----------+
| count(*) |
+----------+
|    13554 |
+----------+
1 row in set (1.22 sec)

mysql> select count(*) from mobiles;
+----------+
| count(*) |
+----------+
|    13554 |
+----------+
1 row in set (0.00 sec)

So: if you have a "weapons" table and it's fairly static, and the same queries are made against it repeatedly, chances are MySQL is going to do your job for you by keeping those queries cached in memory.

However, if your MySQL server is under load and getting a lot of reads and writes - well, it can only cache so much, obviously. So to test for real, you need to simulate heavy load.

(b) If you really wanted to put some static things into arrays, I don't believe it limits any growth potential as codestryke implies. You can still have many-to-many relationships, you just need to reference the index of the array as if it were a row ID.

Imagine you have a table called players and another table called player_items:
players(id:integer, name:string)
player_items(id:integer, player_id:integer, weapon_id:integer, slot:string)

The field weapon_id is just an integer, and references the index of some static array of weapons. The field 'slot' could be left-hand, right-hand, both-hands, or left blank for just "in inventory".

(c) You guys make a good point about PHP being interpreted, and adding a lot of fluff eats up more memory, but even with a static array stuck in a file, it doesn't have to be loaded unless it's necessary. If the player is looking at the forums, there's no reason to load the weapons array.

Personally, I code in Ruby so my scenario is a little different. The entire codebase gets loaded and requests are made against it in a Ruby on Rails app, rather than like PHP where each request involves parsing and interpreting the code in real time.

Whether PHP or not, here are a few other suggestions to reduce unnecessary database hits (altho, these probably not real possibilities for shared hosting, if that's what you use): I like to use memcache for some things because no matter how many instances of your app or scripts are running, they can hit memcache and grab items right out of the same shared section of memory (reducing the bloat that codestryke was warning against). Another option is running a second instance of mysql - one fine-tuned for read-only access. Stick all your static tables in this second instance and hit that up for faster reads, with more of a guarantee that those queries will be cached.

(d) Now that we got all that out of the way, forget all of it, and remember Donald Knuth: "We should forget about small efficiencies, say about 97% of the time: premature optimization is the root of all evil."

If you don't have a full application to profile and test one method against another under actual load, with actual datasets, you're just fixing stuff that may or may not be a problem and you're not developing your game.

[codestryke]

If this was C then arrays would won. We keep forgetting PHP is an interpreted language (at least I keep forgetting ) where ever line of code is very slow no matter how we write it (maybe Zend Optimizer or IonCube would speed it up, but if I remember correcly these cost $1000, so it is out of the question for small/mid size games anyway).

No it would not be faster in C, nor C++, nor Assembly language or any other language you can think of. Look at what Saljutin's test showed.  When comparing only the loops it was equal in task, however, what does take time is allocating the memory to store the array. No matter what language you chose it still takes time to allocate memory, period. So one for one mixing database and array storage is slower then just putting it into the database. With apps written in C etc you have an application memory space where you can hold that memory until the app is finished executing (unlike PHP where it's allocated on every page hit).

Once again, too, you limit yourself to what you can create, easily. Look at the example challenge. To create such a report you would first have to create some multi-array sorting algorithm, get the array's index, then finally execute the query to get the most powerful weapon. You can change this challenge up even more, what about sorting player's by there weapons, now you getting into more "hacks" or work arounds that wouldn't even be a consideration if you put the weapons in the database.

If you start hard coding array index numbers into the game then you really are in a world of hurt if you ever wanted to add a new weapon in the middle of that array! Looking at the original array example, I would never create the array like that either, using numbered indexes instead of named ones is also going to be a long process as you may want to expand that array in the future. Or, like myself, go on to other games and then have to go back to fix or add something to an earlier game and try to remember what the heck $weapons[5][1] is referencing.

[Chris]

No it would not be faster in C, nor C++, nor Assembly language or any other language you can think of. Look at what Saljutin's test showed.  When comparing only the loops it was equal in task, however, what does take time is allocating the memory to store the array. No matter what language you chose it still takes time to allocate memory, period. So one for one mixing database and array storage is slower then just putting it into the database. With apps written in C etc you have an application memory space where you can hold that memory until the app is finished executing (unlike PHP where it's allocated on every page hit).

Well, I assumed no one would be insane enough to recreate an array inside a loop  (or create it more than once) in C

[ckumarjha]

No it would not be faster in C, nor C++, nor Assembly language or any other language you can think of. Look at what Saljutin's test showed.  When comparing only the loops it was equal in task, however, what does take time is allocating the memory to store the array. No matter what language you chose it still takes time to allocate memory, period. So one for one mixing database and array storage is slower then just putting it into the database.

I am a noob programmer and specially have very little understanding of database....

but when queries are made to a database isn't that require to load into memory and then its directed to the calling application?

if that were the case then it would have the same overheads that adds up with array allocation in memory each time a query is made. Was that taken into account in the above test? Though if repeatedly same query is made then that might be cached. I think that is done by all database systems... i am not sure though.

In any case if we mix query with arrays in the above said manner then obviously there will be double overheads instead of the single with any individual method. Correct me if I am wrong.

I personally wouldn't go into the trouble of doing all those things with array because on working with database it will be far easier on maintenance and scalability front.

[Delifisek]

Using SQL as a primary data storage was No 1 stupity of dynamic web programming.

SQL was biggest and baddest performance bottleneck of PHP web programming.

I suspect in the beginning people often faces Unix chown problems in their web hostings, because of that, most of them went to use SQL as data storage. And thats why most of beginner examples are use SQL for state storage.

in short: SQL vast to costly, use it when you need SQL query.

Topic starter was right. Storing data in Array was most effective in PHP. Combining this with any php opcode cache. You will read all your data from Memory.

If you benchmark a web page, try to get 100 request per second. Not just your single request. When your web site goes under load things gonna change.

[Scion]

SQL was biggest and baddest performance bottleneck of PHP web programming.

The DB is generally the bottleneck of all web bassed applications regardless of language.

It still is complete nonsense to try and store relational data in a mix out of DB and arrays... If you have some fairly static reference data that is looked up regularly then if you ask me its looking like a classic case for memcache.

[Fizzadar]

I've definitely seen benchmarks that show at 10,000+ queries arrays smash MySQL in terms of performance, although MEMORY based tables and caching make it a lot faster, it's still no where near the speed of Arrays.

One query might be faster, but thousands is not, from what I've seen. I'll try find the link.

[Scion]

Yes Fizzadar, thats a classic case of use the right tool for the job.

If all youve got is a hammer you can still put a screw in the wall....its just not that efficient and the end result might not be that great....but it works...i know this out of personal experience .....Which is why it pays to get yourself one of those cases with a whole bunch of differnet tools.

As a programmer its no different....you need to make sure that you have a variety of 'tools' in your box of tricks.

If for some bizare reason you do need to make 10,000 selects on a specific table within a single request then you may be right to use an array....but then again what if the table has 2kk rows? then that will change the situation again.

And in any situation where you do need to use relational data in an array then if your not populating that array from the DB in the first place your going to have data corruption issues.....if not sooner then later.

[Fizzadar]

Yeah I know, was just making a point

Database tables are far more useful 99% of the time.