2005 Table Partition - Optimization Test

On the job we decided to implement table partitioning with SQL Server 2005. I wanted to get an understanding of the increases and decreases that we would see with a partitioned table. Below is documentation of my tests.

Tables Involved
Step 1:
Create Table called 'nonPartitioned'
No Primary Key
16 varchar(50) fields
1 bit field called 'activeFlag'

Step 2:
Populated table with random text
Created 250,000 rows

Step 3:
Mark a sizeable portion of the table as active.
I took 50,000 rows and marked them as active.
I took rows that cotained an 'a' in 'fieldG'

Step 4:
This table is partitioned on the activeFlag, 1 or 0
Create Partition Function
Create Partition Schema
Create Partition Table

Step 5:
Copy Data from 'nonPartitioned' to 'partitioned'

Step 6:
Create table called 'smallTable'
This holds only active records
Populate with active records only

Step 7:
Create table 'selected'
This will hold data that we select from either partition

Some important metrics to note at this point:

52,689 where ActiveFlag =1
197,311 where ActiveFlag =0

35.51 MB - Partitioned Table Size
36.17 MB - Non Partitioned Table Size
7.45 MB - Small Table

First Test - Selecting from Small Partition
The first test is to select data and see if we have a speed increase.

• Truncate the 'selected' table
• Perform the following 1,000 times
• Select 10 random records from the nonPartitioned Table where activeFlag = 1
  
• Insert it into the selected table

4:53 (4 min, 53 seconds) - NonPartitioned Table
3:39 (3 min, 39 seconds) - Partitioned Table
3:55 (3 min, 55 seconds) - Small Table

Second Test - Selecting from Large Partition
The first test is to select data and see if we have a speed increase.

• Truncate the 'selected' table
• Perform the following 1,000 times
• Select 10 random records from the nonPartitioned Table where activeFlag = 0
  
• Insert it into the selected table

13:23 - Partitioned Table
12:59 - NonPartitioned Table
00:00 - Small Table (no select available of large set)

The following code was used for those tests:

truncate table selected
declare @count int
set @count = 0
while @count < activeflag="0" count="@count+1">
Third Test - Updating 1,000 rows
I now want to test the update statements to these partitions. I used the following logic

• Truncate the 'selected' table
• Perform the following 1,000 times
• Select 1 random records from the nonPartitioned Table where activeFlag = 1
• Update one field (fieldB) for this record

5:03 - Partitioned Table
9:56 - NonPartitioned Table
4:57 - Small Table

Third Test - Updating 5,000 rows
I now want to test the update statements to these partitions. I used the following logic

• Truncate the 'selected' table
• Perform the following 5,000 times
• Select 1 random records from the nonPartitioned Table where activeFlag = 1
• Update one field (fieldB) for this record

33:17 - Partitioned Table
49:25 - NonPartitioned Table
24:27 - Small Table

Conclusion

If you must have all the data in one table, then partitioning the table will greatly increase your performance in all areas.

However if you can pull the data out of the table into a smaller table, this will give you the greatest performance during transactional processing.

Caveat

This is my own home grown test, as always perform your own test before making production relavant decisions. Your data may give you different results.

Battle Forces Online: Bounce Rates and Visits

Battle Forces Online: Bounce Rates and Visits

Interesting .... Between Feb 15, 2008 and Mar 14, 2008.

Low Bounce Rates:
* freegames.au - 39 visits, 20% bounce rate
* internetgames.org - 1 visits (prev 7)
* onlinegamesinn.com - 10 visits (prev 19), 30% bounce rate
* pbbgblog - 42 visits, 40% bounce rate
* onrpg.com - 133 visits (prev 37), 27% bounce rate (prev 30%)
* forums.indiegamer.com - 13 visits (prev 97), 30% bounce rate (prev 62%)

Most Visits From
* onrpg.com - 133 visits (prev 37), 27% bounce rate (prev 30%)
* community.bbgamezone.com - 49 visits (prev 59), 35% bounce rate (prev 45%)
* forums.indiegamer.com - 13 visits (prev 97), 30% bounce rate (prev 62%)
* pbbgblog - 42 visits, 40% bounce rate

I know that this past month I have posted a lot of entries on pbbgblog.com....evidently they have been working. The bounce rate is a bit high but it seems worth while.

Mental Note: Check out onrpg.com, it may be worth while to post a few things over there as it seems, they have a great bounce rate for my visitors.

Code: Merge Gif Images to create one image

The following code will take an array of gif images and merge them into one and save the resulting image to file.

I use it to create a unit images on the fly. As users buy different equipment, the unit's look changes.

function mergeImages($imgPathArray, $dest)
{
 $imgArray = array();
 for($i=0;$i < sizeOf($imgPathArray);$i++)
 {
  array_push($imgArray, imageCreateFromGif($imgPathArray[$i]));
 }

 $src = $imgArray[0];
 $background = ImageColorClosest($src, 0, 0, 0);

 for($i=1;$i<sizeOf($imgArray);$i++)
 {
  //ImageColorTransparent($imgNew, $background);

  ImageCopyMerge( $src ,
    $imgArray[$i],
    0, //dest x
    0, //dest y
    0, //src x
    0, //src y
    74,//width
    100,//height
    100 //alpha
    );

  imagedestroy($imgArray[$i]);
 }

 return ImageGif($src, $dest);
}

Usage:

$dest = "images/custImage.gif";
$imgPathArray = array("images/builder/blueBase.gif","images/builder/body.gif","images/builder/legs.gif");
array_push($imgPathArray, "images/builder/body.gif");
array_push($imgPathArray, "images/builder/legs.gif");
array_push($imgPathArray, "images/builder/sword.gif");
mergeImages($imgPathArray, $dest);

Notes and Caveats:

All of my gif images were the same size, 74 X 100.
They also have transparent backgrounds.
If your images are different sizes, just change the width and height lines.
Note: Your array must contain at least 2 images otherwise you get an error.

Battle Forces Online - Upgrade Time

Battle Forces Online has gone through a revision.

New Features

• Added Destinations - Inns, Gates and visiting spots
• More boards to play
• Fight Pits - Small maps that once clear earn you loot
• Optimizations in the response time to action requests
  
• You can now buy more Blades (units) and weapons
• Healing Spell added for Mages
• 2 New Blades added, Earth Mage and Fire Mage
• More spells and items
• No more random spawning, everyone enters the board at the same time.
• Alter your party
  

StoryLine

The story line is a bit weak at the moment, but I wanted to build the ability to give each map a storyline.

Destinations
There are now a number of places you can visit on each board, these places allow you to heal up, buy stuff and chat with NPCs.

More Boards to Play
Currently the game consists of the following boards/maps

• Awakening - All new users enter here, there are very few enemies. This board allows users to get used to moving and attacking.
• Dark Forest - Central location, allows you to move around and fight other players. There is a forest theme to it.
• Gorjin's Bridge - A control point, you're going to need to level up and have a party of 2 Blades to clear this map. There is a bridge theme to it.
• Gorjin's Lair - This board was built to test a user. Try to get to the castle at the far end.
• Small Fight Pit - Very small fight pit, good for users with low stats or low party counts.
• Medium Fight Pit - A medium sized pit
• Large Fight Pit - Lots of enemies, lots of space...bring your whole party!
  

Optimizations
Due to the nature of the engine, when you tell a Blade to move or attack, the request is routed to the server, the server then responds with an OK or Not OK. This was causing a delay to occur between when you clicked and when a Blade actually performed the action.

I made some optimizations to the server handling as well as providing a visual cue (The blades says OK and fades a bit) when you give an action command.

Spells
If a spell is not an attack spell, a mage will cast it upon all members of the party. So a heal spell will heal all members by 10 HP. You do not have to target a member to do this.

In addition to this some spells now have a duration, these spell affects will last for a certain length of time. For example: Magic Shield last for 2 min and gives all members in the party +5 defense.

New Blades
I took the old mage and re-named it to "Fire Mage", this mage can cast fire spells.

I created a new mage called "War Mage", this mage cast lighting spells.

I created a new mage called "Earth Mage", this mage cast healing and defensive spells.

Mages can be killed by any warrior pretty easily so protect them.

Spawning
The old system used to randomly place your Blade on a map. The new system has a spawning point and your entire party will spawn around this point every time you join a map.

Editing the Party
You can now edit how your party spawns, so if you want the mage in the back and the Lord in the front you can do this.

Additional editing features are coming.

In the Future

Some things I'd like to develop in the near future:

* Guilds
* Challenges

Question from Bill Gates

I used LinkedIn (a social networking application)

On it Bill Gates had asked a general question; What can we do to encourage young people to pursue careers in science and technology?

My response is below:

It boils down to Respect and Reputation. One of the reasons that young people admire and purse careers is that they see the position as one of respect and authority in the community.

For example, the big time lawyer, the life saving doctor, the helping policeman; these are all positions which come with a large degree of respect and reputation.

Currently I believe that youths tend to view technology jobs as high paying white collar jobs. Reputable well paying positions but not very "exciting".

I think we, the technology group, have done ourselves a dis-service by marketting technology as easy to learn and any one can learn it. This takes away from the respect that one can get for having this job. We may be making it look too easy. I say look because I am well of aware just how hard it is to do things correctly and succeed in our industry, but I wonder how many young adults are aware of this.

Now this is counter to what a lot of folks and groups are doing to encourage technology in our youths. And I certainly would not stop any of these efforts. This might be considered a different tact.

An action that could be taken is to either create another title or pick one that is out there , I believe we have enough, and work at creating an image of that title. An image which will stand beside and out "shine" the Lawyer, Doctor, Buisess Exec career choices that young adults face.

I mean no disrespect to all the great technology leaders out there. I am awed and very respectful of your reputations.

These thoughts come after volunteering teaching young minority kids in the 9 – 12^th graders .NET web development for the past 4 years. This is through a community driven organization. I have found the childgrens thoughts and actions very insightful and for anyone that has not taken the time to give back to the community in this well.

I am no author but these are some things that I thought I should share.

Simplest of Template Systems

I've looked at a number of templating engines over the years. Some are very nice, however they all seem a bit large for what I usually am trying to build. I've used the follow technique which is dead simple.

I put two include statements in each file, one at the top and one at the bottom. Like so:



Main contents and code



That's it. I create my layout and then I look for the large part of the layout where the main content would go, this gives me my "dividing" line. All code above this goes in the templateTop, all code below it goes into templateBottom.

For Example

I have a page, with a menu across the top, some content and a footer. This is how my html might look.

Home | Login | Register | Other

Hello and welcome to the grand site of ....

Copywrite Someone Somewhere

This goes into the top

templateTop.php

Home | Login | Register | Other

This goes into the bottom:

templateBottom.php

Copywrite Someone Somewhere

Draw Backs

There is one drawback to this method, if you use images or have referential links within your template files, they could have the wrong url because your base will always be the file that did the include.

For example, if you have the following line in your template file.


The root of the file included the template will be included with this:

When the file index.php has the include:
But when the file /actions/buyItem.php has the include:

To fix this you must always use fully qualified paths or refer to the root by have a "/" in the front of all referential urls.

Link: Why we banned Lego

A very informative view on how a Lego Town built by kids, leads to a deeper understanding of communities and power structures.

http://www.rethinkingschools.org/archive/21_02/lego212.shtml

Defeating SQL Injection

Your code is the walls which protect your intellectual ideas. The best attack your enemy has is SQL injection. Are your walls providing a suitable defense? (*groan* That was a horrible opening but it was the best I could do)

A few comments on this blog were made on SQL Injection, so I thought I'd post some thoughts. There are many articles about how to prevent SQL injection, I am going to cover just a few techniques.

The Problem
If you don't know what SQL Injection is Google it, but quite simply it is a way for external forces to execute SQL statements on your database. Statements like:

delete from user (scary)
update user set money= 100000000 (hacker)

Most of these types of hacks happen when a user types specific things into a text box or address bar. This being the case you want to "clean" all incoming input. Rule of Thumb: Very rarely trust and always verify. (Trust but verify?)

The reason this hack works is because when you use a variable in a sql statement it can contain malicious code. For example the following piece of codes is suspect to SQL Injection:

$sql = "Select * from user where username = '$username' and password = '$password';"
mysql_execute_query($sql);

If the user uses the following as their username ';delete from user; it will delete all users from your tables.

The Solution
Clean all your variables. I run all variables, regardless of how I use them through a clean function. The clean function is responsible for removing quotes and cleaning up odd characters.

The function takes a variable, cleans it and returns it. If you use this code, please add your own steps to ensure protection of your data, this is a simplistic clean function. Below is some of the function:

function clean($value)
{
$value = trim($value);

$value = strip_tags($value);

$value = mysql_real_escape_string($value);

if (!get_magic_quotes_gpc())
{
$value = addslashes($value);
}

$value = rtrim($value);

return $value;
}

I use this function like so:

$sql = "Select * from user where username = '" . clean($username) . "' and password = '" . clean($password) . "';"
mysql_execute_query($sql);

Another Solution
The other step I take is that I have overwritten the mysql_query function so that it replaces my table names. I wanted to make it difficult for people to guess my table names, so I have the following function:

function executeQuery($sql)
{
$sql = str_replace("s_", "game_", $sql);
$q = mysql_query($sql) or die("SQL Error on $PHP_SELF: " . $sql);

return $q;
}

This replaces any s_ with game_, I might name my table "game_user" however my sql would be select * from s_user.

Hopefully this helps those who have questions about SQL Injection.

Battle Forces Online: Bounce Rates and Visits

Interesting .... Between Jan 15, 2008 and Feb 15, 2008.

Low Bounce Rates:

• internetgames.org - 7 hits, 15% bounce rate
• onlinegamesinn.com - 19 hits, 27% bounce rate
• pbbblog.com - 7 hits, 29% bounce rate
  
• onrpg.com - 37 hits, 30% bounce rate
• forums.indiegamer.com - 97 hit, 62% bounce rate

Most Visits From

• community.bbgamezone.com - 56 visits, 52% bounce rate
• forums.indiegamer.com - 97 hit, 62% bounce rate
• onrpg.com - 37 hits, 30% bounce rate

SQL Select Statements Kept Simple

This is a quick article on writing simple and complex select SQL statements. This primer can be used for mySQL and many other database systems. Please keep in mind this is a primer, please research and read other materials to enhance your knowledge.

For those of you who are wondering, mySQL is used in browser based game design quite often. However from some of the question I see posted in forums some developers don't quite understand the intracity of SQL.

Select Statement

The select statement is used to get information from the database. It consists of 3 parts. The Fields the tables and the where clause.

SELECT [TABLE].[FIELDS]
FROM [TABLE]
WHERE [TABLE].[FIELD] = [VALUE]

select user.email
from user
where user.username = 'mobeamer'

• Fields - Can contain a list of fields that you want to select.
• Table - will contain one, possibly more tables that hold the fields
• Where - This will allow you to restrict the information you receive.

Notice that I surrounded mobeamer with quotes, this is needed for strings and is good practice for other data types. A good rule of thumb is when in doubt add quotes.

Notice that I fully qualified the fields by putting the table name in front of the field name. This is not needed when writing a simple select but it is considered good form AND it will come in handy when you decide to "upgrade" your SQL statement (see joins).

Inner Join

An inner join will allow you to pull information from multiple tables with one query. The syntax for an inner join is as follows:

SELECT [TABLE].[FIELDS]
FROM [TABLE]
INNER JOIN [TABLE] ON [TABLE].[FIELD] = [TABLE].[FIELD]
WHERE [TABLE].[FIELD] = [VALUE]

Let's say you have a user table which contains all the player's information. You also have a units table that contains all the units that a player can have. You need a select statement which will get the player's name and the unit's name. In this instance my unit table has a column called ownerID which holds the userID of the owner.

select user.username, unit.unitname
from user
inner join unit on unit.ownerID = user.userID
where user.username='mobeamer'

Another example:

select user.username, unit.unitname
from user
inner join unit on unit.ownerID = user.userID
where unit.class = 'Warrior'

In most cases your joining field will be named the same in both tables, but I wanted to show how this was not necessary.

Notice, that you must fully qualify fields that exists in both tables. You should be aware when using an inner join, as in the first example, if the user does NOT have any units they will NOT appear in the result set.

Be very careful with inner joins as they ALWAYS restrict the result set. (See outer joins)

Outer Join

An outer join works in the same fashion as an inner join with one exception. The join will NOT restrict the returned set For example, in the example above, a player may not have a unit. In this case, an inner join would not pick up that player's name. An outer join on the other hand would pickup this player.

select user.username, unit.unitname
from user
outer join unit on unit.ownerID = user.userID
where user.username='mobeamer'

This will get all user, regardless of how many units they have.

Notice, the first table in the select statment begins the result set. Every outer join from there on can only add rows or columns to the recordset.

A good rule of thumb is to always use an outer join as you will never lose data with an outer join.

How I do It

This is how I write a complicated sql statement, this may not be best practices but I think it may add some context.

I wanted to create a page, which displays a player's profile. I knew I needed a number of fields from the player's table, unit's table and item's table. (Items are things that the unit holds)

I knew I wanted to display all players that had registered, so I started there.

Select user.username, user.numKills
from user
where user.isRegistered = 'Y'

I then wanted to display the unit's name, class and life

Select user.username
, user.numKills
, unit.unitName
, unit.class
, unit.life
from user
left outer join unit on unit.ownerID = user.userID
where user.isRegistered = 'Y'

I then wanted to display all the items that the unit held, and the item's description

Select user.username
, user.numKills
, unit.unitName
, unit.class
, unit.life
, item.itemName
, item.itemDescription
from user
left outer join unit on unit.ownerID = user.userID
left outer join item on item.unitID = unit.unitID
where user.isRegistered = 'Y'

This was the sql that I ended with.

Further Articles
Good ideas to follow this up with:

• Updates, Inserts and Deletes
• Restricting an inner or outer join
• Links and Resources