How to implement fire-and-forget SQL Statements in SQL Azure

Implementing a Fire Hose for SQL Azure

While I was looking around in various blogs, someone was looking for a way to insert records in SQL Azure as fast as possible. Performance was so important that transactional consistency was not important.  So after thinking about that for a few minutes I designed  small class that provides extremely fast queuing of SQL Commands, and a background task that performs the actual work. The class implements a fire hose, fire-and-forget approach to executing statements against a database.

As mentioned, the approach consists of queueing SQL commands in memory, in an asynchronous manner, using a class designed for this purpose (SQLAzureFireHose). The AddAsynch method frees the client thread from the actual processing time to insert commands in the queue. In the background, the class then implements a timer to execute a batch of commands (hard-coded to 100 at a time in this sample code). Note that while this was done for SQL Azure, the class could very well be used for SQL Server. You could also enhance the class to make it transactional aware and rollback the transaction on error.

Sample Client Code

First, here is the client code. A variable, of type SQLAzureFireHose is declared on top. The client code inserts each command to execute using the AddAsynch method, which by design will execute quickly. In fact, it should take about 1 millisecond (or less) to insert 100 items.

SQLAzureFireHose Class

The SQLAzureFireHouse class implements the timer (that executes every second) and the asynchronous add method. Every second, the class fires the timer event, gets 100 SQL statements queued in the _commands object, builds a single SQL string and sends it off to SQL Azure (or SQL Server). This also minimizes roundtrips and reduces the chattiness of the application

 

Print | posted @ Tuesday, September 28, 2010 8:36 PM

Comments on this entry:

Comments are closed.

Comments have been closed on this topic.