SQL Azure insert speed vs queues

Question

I have a set of web roles that insert a row into a SQL Azure database for every page request. The row is about 500 bytes in size.

 

Which would be quickest to insert that row SQL Azure or a queue? If the difference is more that 50 milliseconds, I'd switch!

 

Cheers

 

David

Answer 1

Architecturally I'd recommend using queues for this over SQL Azure. Queues will scale more reliably as your application grows. As SQL Azure comes under load, its more likely to slow down in response. Furthermore, you can then setup one or more worker roles that are responsible for tasks like reading the messages off the queue and updating either SQL Azure or whatever other form of storage you use to aggregate the data.

Another alternative, you might want to look at Runtime intelligence. A copy of the "free" version of this tool is included in VS2010. if you're tracking usage information, that product might give you what you're after without alot of additional work. http://www.preemptive.com/products/runtime-intelligence/overview

Answer 2

Hi David, have you had a chance to test out the speed of queue vs sql azure?

Are there other characteristics you care about? like transactional garantees, reordering based on priority, being able to query the queues as opposed to picks things off the queue etc.

thanks

-cihan

Answer 3

I just want to insert the row as quickly as possible. The order of insertion doesn't matter, querying doesn't matter, transactional guarantees don't matter.

The data will eventually end up in a SQL Azure database. The quesion is: is it faster to add to a queue, then asynchronously insert into SQL Azure or insert directly into a SQL Azure database? A secondary consideration is scalability, but I'm less concerned about that as I think I can just scale out with multiple queues or multiple SQL Azure databases. The service would have to deal with up to 100-300 inserts per second, part of this could be mitigated by having multiple services in multiple datacentres and using anycast DNS to serve to the "nearest" datacentre.

Cheers

David

Answer 4

I just did some analysis overnight on the performance of SQL Azure vs queues.

My page took around 240-430ms longer to download when I added the queue code. When you compare that with SQL Azure's fairly consistent 100-120ms additional time, SQL Azure wins hands down!

I've also found these two articles on queues:

http://social.msdn.microsoft.com/Forums/en-US/windowsazure/thread/f50a70d5-1832-46e0-a457-31287c4fefbc

On the last post in this discussion Brad Calder apparently mentions around 100ms to add a small message into a queue with a chance that it could take seconds during heavy load.

http://bstineman.spaces.live.com/blog/cns!61AEA8168D26EA6B!312.entry?wa=wsignin1.0&sa=738387388

In this blog post there is mention of 345ms to add a message to a queue.

Sounds to me like I should stick with SQL Azure!

Anyone had any other experiences? I must say I generally use a SQL database to hold the equivalent of queues in my applications and it seems like they are faster especially if the data needs processing in a SQL database in the end anyway...

Cheers

David

PS Raymond-Lee: you seem to have merged this post with another seemingly unrelated question, was this an error?

Answer 5

Is there any chance the insert operation can be done asyncronously (so page load isn't blocked during execution)? This would help speed up your page performance and since you don't appear to be overly worried about transaction control it might be an option.

If the immediate availability of the information isn't important, using an async insert into a queue may give you both the speed and scalability you'd need for your solution.

Answer 6

Hi Brent

I'd love to do the insertion asynchronously, but inserting in a queue is slower that inserting into SQL Azure - as per the links in this thread and my own research. How would I do it faster than inserting directly into SQL Azure?

Cheers

David

Answer 7

if you do the operation asynchronously, you basically fire off the insert request and then continue doing whatever you need to do without waiting for that operation to complete. You may want to give these a read:

http://msdn.microsoft.com/en-us/library/ee741701.aspx

http://msdn.microsoft.com/en-us/magazine/ee335721.aspx

In a nutshell, you call the "BeginAddMessage" method, the code returns almost immediately, then at a later point when the insert has completed, a callback method is fired which could do anything or nothing depending on your needs. The key point is that once your return from BeginAddMessage, you can continue loading your page with almost no measurable delay.

 

Answer 8

Thanks Brent

I've been looking at your links and searching online and there doesn't seem to be any sample code on how to use the BeginAddMessage or any of Azure's asynchronous methods in a web role.

Have you found any sample code to do this?

Cheers

David

Answer 9

shouldn't be all that different. I'll see what I can put together. Give me a couple days and I'll get back too you. I put together my own rest calls last summer so this can't be more difficult than that. :)

Answer 10

I still need to test it, but here's my sample code:

    protected void Page_Load(object sender, EventArgs e)
    {
      CloudStorageAccount cloudStorageAccount = CloudStorageAccount.DevelopmentStorageAccount;
      CloudQueueClient cloudQueueClient = cloudStorageAccount.CreateCloudQueueClient();
      CloudQueue cloudQueue = cloudQueueClient.GetQueueReference(queueName);
      cloudQueue.CreateIfNotExist();

      AsyncCallback callBack = new AsyncCallback(AddMessageComplete);

      long startTime = DateTime.Now.Ticks;

      for (int i = 0; i <= 1000; i++)
      {
        CloudQueueMessage tmpMsg = new CloudQueueMessage("test message");
        cloudQueue.BeginAddMessage(tmpMsg, callBack, null);
      }

      long endTime = DateTime.Now.Ticks;

      // calculate differnce in milliseconds
      long difference = (endTime - startTime) / 10000;

      string tmp = string.Empty;
    }

    static void AddMessageComplete(IAsyncResult ar)
    {
      // do something
      string tmp = string.Empty;
    }

Answer 11

ok, sample code ran fine. running the app locally but accessing a hosted storage account it came back with a value of 3496ms to insert the 1000 sample queue messages. Or roughtly 3.4ms per queue message. Furthermove, if I move the creation of the message outside of the loop (so I get a more accurate reflection of the time required to just do the BeginAddMessage calls... I ca down into the 200ms (for all 1000 calls) timeframe.

Mind you... its entirely possible to build an asynchronous method of inserting messages into SQL Server as well. Just takes a bit more effort.

Answer 12

Ran a couple more tests.... you can pass "null" as the value for the callback parameter if you absolutely don't care to do any processing after the operations actually complete. Furthermore, in monitoring the queue depth, it took 10-15 seconds before all 1000 messages had been sent into the queue and were visible.

Answer 13

Great stuff Brent, thanks!

I'm running the code overnight and I'll tell you how it goes.

Cheers

David

Answer 14

Hi Brent

Here are the results:

http://i902.photobucket.com/albums/ac227/DavidM51/AzureQueuePerformance.png

PH/PG: two of our conventional datacentres in the UK

AZ-EUNO: an Azure service in the Northern Europe datacentre

AZ-EUNO - Q Test: an Azure service in Northern Europe using the asynchronous queue code you supplied

The asynchronous queue code is faster, but slightly erratic. I might try asynchronous SQL Azure insertion code you suggested next...

Cheers

David

Answer 15

I can't see your code so I can't speak to it directly... however, I'd recommend full instrumenting your pages to make sure you know exactly where any possible bottlenecks may be. I really doubt it could be the async AddMessage that's causing any spikes in load times.

Answer 16

Hi David - I know this is an older thread, but one thing I would recommend to maximize speed is to batch your inserts into SQL Azure. You could build a simple caching layer holding pending INSERT statements. The goal of this caching layer would be hold a series of INSERT statements in memory (say 100), then have a background thread "flush" the commands in a single request by performing a bulk INSERT.  Your client code would not wait longer than a few milliseconds, if that.

UPDATE:
I decided to implement this logic and try it out. The code uses a "fire hose" class that queues SQL commands in memory, which are added asynchronously; then a timer runs in the background and executes 100 commands at a time (hence minimizing round trips). You can check out this post which provides the complete example: http://geekswithblogs.net/hroggero/archive/2010/09/28/how-to-implement-fire-and-forget-sql-statements-in-sql-azure.aspx 

 

---

Herve Roggero Blue Syntax - Azure Consulting Services Co-Author: Pro SQL Azure