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