Multiple concurrent calls to SqlCommand.BeginExecuteNonQuery using same SqlConnection

Question

I have some working C# code that uses a SqlConnection to create temp tables (e.g., #Foo), call stored procs to fill those temp tables and return results to the C# client, use c# to perform complex calculations on those results, and use the calculation results to update one of the temp tables created earlier.

Because of the temp tables used throughout the process, we must have only one SqlConnection.

I identified a performance bottleneck in updating the temp table with the calculation results. This code was already batching the updates to prevent the C# client from running out of memory. Each batch of calculated data was sent to a stored proc via SqlCommand.ExecuteNonQuery, and the sproc in turn updates the temp table. The code was spending most of its time in this call to ExecuteNonQuery.

So, I changed it to BeginExecuteNonQuery, along with the code to wait on the threads and call EndExecuteNonQuery. This improved performance by about a third, but I am worried about having multiple concurrent calls to SqlCommand.BeginExecuteNonQuery using the same SqlConnection.

Is this OK, or will I run into threading problems?

Sorry for the long explanation.

The MSDN docs state:

The BeginExecuteNonQuery method returns immediately, but until the code executes the corresponding EndExecuteNonQuery method call, it must not execute any other calls that start a synchronous or asynchronous execution against the same SqlCommand object.

This seems to imply that different SqlCommand objects can call BeginExecuteNonQuery before the first SqlCommand completes.

Here is some code that illustrates the issue:

    private class SqlCommandData     {         public SqlCommand Command { get; set; }         public IAsyncResult AsyncResult { get; set; }     }      public static void TestMultipleConcurrentBeginExecuteNonQueryCalls(string baseConnectionString)     {         var connectionStringBuilder = new SqlConnectionStringBuilder(baseConnectionString)                                           {                                               MultipleActiveResultSets = true,                                               AsynchronousProcessing = true                                           };         using (var connection = new SqlConnection(connectionStringBuilder.ConnectionString))         {             connection.Open();              // ELIDED - code that uses connection to do various Sql work              SqlDataReader dataReader = null;                 // in real code, this would be initialized from calls to SqlCommand.ExecuteReader, using same connection              var commandDatas = new List<SqlCommandData>();             var count = 0;             const int maxCountPerJob = 10000;             while (dataReader.Read())             {                 count++;                 // ELIDED - do some calculations on data, too complex to do in SQL stored proc                 if (count >= maxCountPerJob)                 {                     count = 0;                     var commandData = new SqlCommandData                                           {                                               Command = new SqlCommand {Connection = connection}                                           };                     // ELIDED - other initialization of command - used to send the results of calculation back to DB                     commandData.AsyncResult = commandData.Command.BeginExecuteNonQuery();                     commandDatas.Add(commandData);                 }             }             dataReader.Close();              WaitHandle.WaitAll(commandDatas.Select(c => c.AsyncResult.AsyncWaitHandle).ToArray());             foreach (var commandData in commandDatas)             {                 commandData.Command.EndExecuteNonQuery(commandData.AsyncResult);                 commandData.Command.Dispose();             }              // ELIDED - more code using same SqlConnection to do final work              connection.Close();         }     } 

Answer 1

Well, at the extreme risk of receiving a lot of down votes I have to comment on this one. Firstly, this is a good question and well stated to address the specific potential issue you mentioned. However, you have neglected to discuss this "lengthy" process you're trying to accomplish.

My experience has thought me one thing...

If the question your asking is hard to answer, change the question.

Though I know very little of your specific problem, I think this neatly applies to your predicament. As others have mentioned... Temp tables are nasty, creating your own tables for a specific task is nastier still, updating large quantities of data in SQL is expensive.

Ask yourself "Can you avoid it all?"

Quite often people choose to implement extremely complicated logic in databases because they have a belief that SQL can do it faster. Practically this is a flawed concept, Databases are storage/serialization devices, they are good at storing, updating, locating, and synchronizing access to data. They are not well equipped for processing complex operations. Even after Microsoft's (and others) bastardization of the database by injecting full development languages into it, It cannot perform as optimally as a well written client (*depending on the complexity of the operations, which I suspect you have surpassed).

As an example, you have a database of around 2gb worth of raw data. You want to produce a complex report or analysis on the entire data set. Well simply put 2gb of memory is easy to come by, slurp the entire database (or the portion you need) into memory using dictionaries or whatever to create the look-ups you will need. Depending on several factors the whole thing will likely run several times faster than SQL, can easily be unit tested, and will be (IMHO) significantly easier to build, debug, and maintain than a nasty assortment of SPROCs constructing dynamic SQL. Even with more than 2gb of raw data, client caches can easily be created with several existing technologies (B-Trees, ISAM, or the like).

The product I work on today has 2.4tb of data in the database and we have not a single sproc, join statement, or even a non-equality where clause.

But alas my advice may or may not be pertinent to your specific circumstances since I do not know your objectives or constraints. Hopefully, if nothing else, it will make you ask yourself:

"Am I asking the right question?"

Answer 2

You could use a producer-consumer pattern with 2 threads and 2 simultaneous but independent sql connections.

• ConcurrentQueue in .NET 4: http://msdn.microsoft.com/en-us/library/dd267265.aspx
• Concurrency enable queue for .NET 3.5 (on Stack Overflow)

The producer (1st thread) has the DataReader (1st sql connection) and writes its results to a blocking queue. The consumer (2nd thread) reads from the queue, has the ExecuteNonQuery (2nd sql connection) and writes to the temp table(s).

Another idea in case your ExecuteNonQuery commands are basically multiple INSERTs: ExecuteNonQuery has an overload with a StringCollection to send multiple sql statements as one operation.