Monday, August 31, 2015

How to generate smaller snapshots when adding new articles to SQL Server replication

We had SQL Server transaction replication configured in our environment.  I wanted to add new articles to the publication.

To do this efficiently, I wanted to initialize only the new articles.  I wanted to avoid taking a full snapshot of all the existing articles all over again.

I googled around and found a pretty simple way in which this can be achieved.

How Do They Do It!

This can be achieved in four simple steps.

Add the new articles to the transaction replication setup

To add new articles to the publication either you can use the Management Studio interface or use the following script

Turn off "allow_anonymous" and "immediate_sync" properties on the publication

To take a smaller and faster snapshot this is the single most important step.  We need to disable "allow_anonymous" and "immediate_sync" properties of the publication.  To do this use the following script

Start the Snapshot agent

We need to start the snapshot from the replication monitor as follows


Once this agent completes you will notice that it has created a snapshot with only the newly added article.  This is super fast and saves disk space as well.  This is how it looks after the Snapshot agent has finished running.


Turn on "allow_anonymous" and "immediate_sync" properties on the publication

This is the last and final step which reverses the Step 2.  The script to do this is also exactly opposite of what we did in Step 2

For bigger databases it saves a hell lot of time.  I hope this saves you some time as it did for me!

Friday, July 31, 2015

Fastest way to parse XML in SQL Server

In one of my projects, we had a need to parse XML and populate a temporary table.  The stored procedure was supposed to be written in SQL Server.

As we know, there are multiple ways in which we could parse an XML in SQL Server, I wanted to use the one that performs fastest.

I looked around to see if I could find some sort of performance numbers for each of the method.  After a bit of googling, I found out a few places where there were bits and pieces of information which compared the different approaches.  Taking inspiration from this thread, I wrote a simple test which prints out the performance stats for parsing our XML using three different approaches.

The test SQL itself has nothing specific to our application,

  • It basically generates a very big XML so that we can have a decent comparison between three approaches.
  • It then parses this XML using different techniques
  • It uses following three approaches to parse the XML
    • OPENXML
    • The nodes() with text() 
    • The nodes() without text()
  • It then spits out the performance stats for each of the approach.

Here is the Sample test SQL file.
Sample Output
As you can see:

  • nodes() without text() was the slowest of all
  • Then came OPENXML
  • nodes() with text() was significantly faster than even the OPENXML.
Obviously, we decided to go with nodes() with text() approach.  Use the sample test SQL and find for yourself which one performs best for your requirement.  That's all folks!

Monday, June 29, 2015

How To access multiple SQL Server databases without MS DTC

In one of my .NET projects, we had the following situation.
  • We wanted to access multiple SQL Server databases hosted on the same SQL Server instance 
  • These databases had to be accessed via EntityFramework (EF)
  • And from a single transaction.
Sounds simple enough?  

Only problem is, as soon as we use two or more connection objects in the same scope, the transaction is elevated to a distributed transaction (DT).  Such transactions are handled by Microsoft Distributed Transaction Coordinator (MS DTC).

All is well, if you really need DT.  But in our setup, we were sure that all these databases will be hosted on a single SQL Server instance.  I really wanted to avoid elevating the transaction to DT, obviously wanted to avoid the performance penalty.

I went looking for answers, on how to achieve this, after some searching and hacking, I was able to achieve this (here is the SO post that explains the solution).  So here's how its done!

How Do They Do It!

It turned out to be pretty simple in the end.  All that is needed is to make sure that we do not instantiate more than one DbConnection objects in one scope!  

Well, then the key question is, How do we use the same DbConnection object, to talk to two different EF contexts - which access two different databases?  

Let me explain this with an example:
  • Say that you need two EF context instances
  • EF1 connects to DB1 and EF2 connects to DB2.
  • Pseudo code that will elevate the transaction to DT looks like this
  • To avoid DT all we need to do is, avoid EF2 from creating its own connection, instead use the Connection instance created by EF1 in someway.
  • We can get hold of the DBConnection instance from EF1
  • Change the database using the ChangeDatabase method
  • And use this DBConnection instance to build the instance of EF2
  • Updated pseudo code looks like this

That's about it!  A simple cheap trick to avoid paying the performance penalty of MS DTC!

Saturday, May 30, 2015

How we reduced the size of our iOS app Monster Math from ~108MB to ~39MB - Part 3

This is the third and final post in the series of posts where, I will explain, How we reduced the size of our iOS app Monster Math from around 108MB to 39 MB.

In the previous post, I explained how we first converted our 24bit PNG images to 8bit PNG and then converted those 8bit PNG images to WebP images.

If you didn't get a chance to catchup on this series yet then, I suggest first read Part 1 and then Part 2 and then continue reading this last post.

Convert All Sounds and Music files from WAV (or any other format to) AAC Format

I said this in the first post and I will say it again, "WAV files are huge, don't use them period!".

We converted all the WAV files into AAC format.  This gives us enormous savings of around 70-80% per file!

To convert WAV files to AAC, we will use the Audio File Converter (afconvert) tool which comes pre-installed with MAC.  A small shell script to convert all WAV files to AAC would look like this
This simple shell script will find all the WAV files in a given directory and passes them to afconvert which converts them into AAC format.  

The results are awesome, you got to see it to believe it!

Reducing bit rate of AAC Voices to 32K

We had like a ton of voices in our app in AAC format.  Reducing the bit rate of all these AAC voices to 32K give us another 70-80% reduction in the file size!

To reduce the bit rate we will use the same afconvert command with a slight modification.  This command has an option via which we can specify the bitrate to be used.
Only real difference between the previous and this script is the option "-b 32000".  This will make sure that all the converted files will have 32000 as the bit rate!

These were the last two important methods that gave us significant size reductions in the final binary.

Thats all folks, have fun reducing the size of your binaries!

Thursday, April 30, 2015

How we reduced the size of our iOS app Monster Math from ~108MB to ~39MB - Part 2

This is the second post in the series of posts where, I will explain how we reduced the size of our iOS app Monster Math from around 108MB to 39 MB.

The previous post, I described the steps we took to get the size improvements, in this post I will try explain a few steps in a bit more detail. 

Converting 24bit PNG's to 8bit PNG's

Of course its possible to update each image manually and save it as 8bit PNG using PhotoShop, but let me put it this way, it wont be convenient, doing it this way.  We had like a ton of images to be converted, so best option would have been a command line tool that we could invoke using a shell script.

With that in mind, we used pngquant a command line utility that does lossy compression of PNG files.  It conveniently converts 24bit PNG files into 8bit PNG files with a simple command.  

This simple shell script will find all the PNG files in a given directory and passes them to pngquant and converts them into 8bit PNG images.  

The results are pretty sweet, it save around 70% of the size of the original 24bit PNG image.

Converting 8bit PNG's to WebP

As mentioned in the earlier post, WebP is an image format that provides lossless and lossy compression for images.  Using the lossless compression on an 8bit PNG gives a whooping 26% reduction in image size.

To convert PNG images into WebP download and install the utilities provided by Google from here.  The simple shell script to convert PNG images to WebP looks like this
This script basically finds all the PNG images in the folder and passes it on to the WebP converter and creates new files with .webp extension.

To render webp images using UIImage use the iOS-WebP library or any other similar library.  Our game uses Cocos2d, to add WebP support to Cocos2d just refer this post

These were the two important methods that gave us significant size reductions in the final binary.  In the next post, I will talk about how we reduced the size of music and sound files of our game.

Sunday, March 29, 2015

How we reduced the size of our iOS app Monster Math from ~108MB to ~39MB - Part 1

Somewhere in November we released our iOS app Monster Math.  When we first released the app it was about 108MB large.  With so many high resolution images and sound and music files that it was bound to be a big binary file.

We knew from the very beginning that, having a binary this big is not ideal.  Some of the common problems with having big binaries are:
  • The time taken for end users to download and start using the app, linearly increases with size of binary.
  • People just don't sit around and watch the app being downloaded, they browse/download other apps, do other stuff, if the download takes longer we might end up getting a download but losing a user.
  • Not to mention the space that it takes up on the device.  Might not be such a big problem for the iPads but certainly a concern for iPhones.
  • Time taken to distribute the alpha/beta/prod builds increases with the size of binary.
These are just a few points but the list can go on and on.  After a few releases, we started actively looking for ways to reduce the size of our binary.  We evaluated numerous options and finally managed to reduce the binary size to around 39MB only!

How Do They Do It!

This post is a developer log of all the things we did to actually get us such great savings on the binary size!

NOTE: Detailed examples of how to do each of the following steps will be covered in subsequent posts.

Identify Biggest Assets In The App
  • This one is by far the easiest step of all, we can easily do a file system sort by size descending, on all the files that get shipped into your application.
  • No surprises here, as expected, biggest chunk comes from
    • Image files
    • Music files
    • Sound files

Converting 24bit PNG's to 8bit PNG's
  • All images in our application were in PNG format.  
  • One option was to convert them to JPEG to reduce the size, but I was not too keen on that.
  • Digging a bit deeper we realised that all our images were PNG 24 bit.  
  • We changed all of them to PNG 8 bit
  • We could not see any huge drop in quality - not that we are any experts on the topic - but for a regular user it didn't make much difference.
  • This single step will get you around 70-80% reduction in size of all the image assets in your application.  Awesome isn't it!

Converting 8bit PNG's to WebP
  • We were still not satisfied with our final binary size, so we went hunting for more optimisations.
  • What we stumbled upon was a new image format called WebP from google.
  • Its an awesome format, provides lossless and lossy compression for images.
  • The lossless compression is a whooping 26% smaller than the PNG's.
  • To use WebP in your iOS app you will need to integrate iOS-WebP or a similar SDK.
  • This added awesomeness, did come with some headaches.  
    • The performance of the game took a bit of a hit, because of added overhead of decoding WebP images
    • This was more apparent when bigger images needed to be decoded for e.g. the backgrounds of scene.
    • To get around this problem we eagerly decoded some of the most commonly used WebP images into PNG's on first launch of the game.
    • And from within the game we use these decoded PNG images.  This solved the performance issue to a great extent.
I know you are itching to look at the code of how we did all this, but please bare with me till the subsequent posts to see the details of each step.


Convert All Sounds and Music files from WAV (or any other format to) AAC Format
  • WAV files are huge, don't use them period!
  • Converting them to AAC format gave us big savings around 70-80% on each file.

Reducing bit rate of AAC Voices to 32K
  • We noticed that reducing the bit rate of all voices (yep! we have voice overs in our app) to around 32K didn't impact the quality much (Note that we only reduced the bitrate of Voices not the background music).  
  • They felt the same when they were played back - again no experts on the subject - but for a normal user it didn't seem to make much difference.
  • This give us another 70-80% reduction in size per voice file!
Seem like a lot of work?  But the end result is equally rewarding, we did manage to reduce the size of our binary to be just under 40MB!

We started from ~108MB and came down to ~39MB, its like more than 63% reduction in the final size!

As mentioned early, watch out for the next set of posts where I will explain in detail on how to perform each of the above steps with code examples.  Till then, keep rocking!

Saturday, February 28, 2015

How to automatically set DateTimeKind, while fetching Datetime properties from database

In one of the my projects, we were saving all DateTime properties after converting them into UTC timezone.  However while reading these values (using EntityFramework) the DateTimeKind was reset to DateTimeKind.Unspecified.

Needless to say that, this behavior causes a lot of problems when we want to convert these DateTime properties into different timezone values.  What we wanted was, an automatic way to set the DateTimeKind value of our DateTime properties to UTC.

I found an awesomely clean way to do this, without doing it manually for each and every DateTime property.

How Do They Do It!

To get the desired effect we need to follow these steps.

  • Create an Attribute that will have the responsibility to set the DateTimeKind value of all eligible DateTime properties of an entity (essentially, those DateTime properties that have this Attribute)
  • Apply this attribute on the desired DateTime properties of all entities.
  • Lastly, we need to hook up this attribute to the ObjectMaterialized event.  So that whenever an entity is materialized, the attribute can perform its job.
Three simple steps to get done, lets jump right into the code.

The code for this attribute is pretty self explanatory, basically
  • It just finds all properties of an entity of type DateTime or DateTime?
  • Loops over these properties to find whether they declare the Attribute
  • If yes, then set the value of the property along with the desired DateTimeKind value.
All we are doing here is to apply the attribute to a few properties of our EntityFramework entities.

Hooking up the Attribute with the ObjectMaterialized event of ObjectContext.

As mentioned earlier, its a simple and clean way to getting the DateTimeKind value set for all DateTime properties of EF entities.

That's all folks!

Credits: SO
Have some Fun!