Multiple concurrent calls to SqlCommand.BeginExecuteNonQuery using same SqlConnection

Viewed 16376

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();
        }
    }
8 Answers

I'm not sure if this question is still important, but...

Try moving the line -

connection.Open();

down from the beginning of your using, to after the new sqlCommand but before the BeginExecuteNonQuery.. like this -

                var commandData = new SqlCommandData
                                      {
                                          Command = new SqlCommand {Connection = connection}
                                      };
                connection.Open();
                // ELIDED - other initialization of command - used to send the results of calculation back to DB
                commandData.AsyncResult = commandData.Command.BeginExecuteNonQuery();
Related