Performance of inserting data: SQL Azure vs Table Storage
Which is faster, SQL Azure or Table Storage? I thought it would be SQL, so I constructed a test to find out. I wrote a console application that inserted 1000 records into SQL Azure (1GB web edition), then the same number into Table Storage. The data was very simple (two small fields), the application used a single thread, and looped 1000 times running one insert statement at a time.
Everything was within the North Europe data centre, the test was run on a small compute instance.
The results:
SQL = 3 seconds
Table Storage = 207 seconds
The SQL insert was a raw SQL statement, whereas the Table Storage was using the SDK, and therefore using an object which had to be serialized. So, whilst it’s not a completely fair test, I don’t think the time was lost in serialization.
What this test doesn’t show is high parallelization. If you had many processes writing simultaneously to SQL Azure, you will eventually hit a bottleneck. With table storage this threshold is in theory much higher.
My code was very badly written. In real life you would parallelize, async and batch, but it shows that Table Storage, the very cheap, highly scalable storage resource can be a bit slow.
Lee Smith are discussing. 
Lee Smith 1:27 am on November 22, 2011 Permalink | Log in to Reply
Really? Try saving in 1 batch (SaveChangesOptions.Batch)
Richard 9:53 am on November 23, 2011 Permalink | Log in to Reply
Hi Lee, thanks for your comment.
You’re right, there are a number of ways to improve the performance of my code, but what I was trying to do was understand which technology was quicker for a single write operation. I exaggerated the effect by repeating it a thousand times. Sorry for not making this clearer.
Perhaps I’ll write a post showing how quick table storage can be if used correctly!
Rich.
franksz 4:07 pm on April 16, 2014 Permalink | Log in to Reply
Apparently this is because you are appending records to the same partition. This is a no-no. Also Azure restricts writes to the same partition to a few hundred per second. To different partitions the account should allow several thousand per second.