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 fh = new SQLAzureFireHose(); private void button1_Click(object sender, EventArgs e) { System.Diagnostics.Stopwatch sw = new System.Diagnostics.Stopwatch(); sw.Start(); for (int i = 0; i < (int)numericUpDown1.Value; i++) fh.AddAsynch("SELECT GETDATE()"); sw.Stop(); labelTime.Text = sw.ElapsedMilliseconds.ToString() + " ms"; }
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.
using System.Threading; namespace SQLAzureFireHose { public class SQLAzureFireHose { private string _connection = @"server=localhost\sql2008;database=enzoshard;integrated security=true"; private SqlConnection _sqlConnection = null; private System.Collections.Queue _commands = new Queue(); private System.Timers.Timer _timer = new System.Timers.Timer(1000); public SQLAzureFireHose() { _timer.Elapsed += new System.Timers.ElapsedEventHandler(_timer_Elapsed); _sqlConnection = new SqlConnection(); _sqlConnection.ConnectionString = _connection; _timer.Start(); } void _timer_Elapsed(object sender, System.Timers.ElapsedEventArgs e) { // Execute the call... _timer.Enabled = false; try { // Only execute the command if the connection is not already used by another thread if (_sqlConnection.State == System.Data.ConnectionState.Closed && _commands.Count > 0) { _sqlConnection.Open(); string sqlCommand = ""; SqlCommand cmd = _sqlConnection.CreateCommand(); int i = 100; string sql = ""; int count = 0; while (i-- > 0) { lock (_commands) { if (_commands.Count == 0) break; sql = (string)_commands.Dequeue(); } count++; sqlCommand += sql + Environment.NewLine; } cmd.CommandText = sqlCommand; cmd.ExecuteNonQuery(); _sqlConnection.Close(); System.Diagnostics.Debug.WriteLine("Sent " + count.ToString() + " commands to the database"); } } catch { if (_sqlConnection.State != System.Data.ConnectionState.Closed) _sqlConnection.Close(); } _timer.Enabled = true; } public void AddAsynch(string command) { ThreadPool.QueueUserWorkItem(new WaitCallback(Add), command); } public void Add(object command) { lock (_commands) _commands.Enqueue((string)command); } } }