使用相同的SqlConnection多个并发调用SqlCommand.BeginExecuteNonQuery [英] Multiple concurrent calls to SqlCommand.BeginExecuteNonQuery using same SqlConnection
问题描述
我有一个使用一个SqlConnection创建临时表（例如，#Foo）的一些工作C＃code，调用存储的特效来填补这些临时表，并返回结果到C＃的客户端，使用C＃中执行复杂的计算这些结果，并使用计算结果来更新以前创建的临时表中的一个。
在整个过程中使用的临时表，因为，我们必须只有一个SqlConnection的。
我确定了更新的临时表中的计算结果的性能瓶颈。这code已用尽的内存配料的更新，以prevent C＃的客户端。每批计算出的数据被通过SqlCommand.ExecuteNonQuery发送到存储过程，并依次存储过程的更新临时表。在code的花费大部分时间在此调用的ExecuteNonQuery。
所以，我把它改为BeginExecuteNonQuery的，随着code等待的线程并调用EndExecuteNonQuery。约三分之一这个改进的性能，但我很担心有多个并发调用SqlCommand.BeginExecuteNonQuery使用相同的SqlConnection。
这是正确的，或者将我遇到线程问题？
抱歉长的解释。
MSDN文档状态：
该BeginExecuteNonQuery的方法立即返回，但直到code执行相应的EndExecuteNonQuery方法调用，它不能执行该启动对同一SqlCommand对象同步或异步执行其他任何电话。
这似乎意味着不同的SqlCommand对象可以调用BeginExecuteNonQuery的第一的SqlCommand完成之前。
下面是一些code，说明了这个问题：
私有类SqlCommandData
{
公共SqlCommand的命令{获得;组; }
公共IAsyncResult的AsyncResult {获得;组; }
}
公共静态无效TestMultipleConcurrentBeginExecuteNonQueryCalls（字符串baseConnectionString）
{
VAR connectionStringBuilder =新SqlConnectionStringBuilder（baseConnectionString）
{
MultipleActiveResultSets = TRUE，
AsynchronousProcessing =真
};
使用（VAR连接=新的SqlConnection（connectionStringBuilder.ConnectionString））
{
connection.Open（）;
//省略 - 使用的连接做各种SQL工作code
SqlDataReader对象的DataReader = NULL;
//在真实code，这将被从调用SqlCommand.ExecuteReader初始化，使用相同的连接
VAR commandDatas =新的名单，其中，SqlCommandData＆GT;（）;
变种数= 0;
const int的maxCountPerJob = 10000;
而（dataReader.Read（））
{
算上++;
//省略 - 做一些数据的计算，过于复杂，做SQL存储过程
如果（计数＆GT; = maxCountPerJob）
{
计数= 0;
VAR commandData =新SqlCommandData
{
命令=新的SqlCommand {连接=连接}
};
//省略 - 命令的其它初始化 - 用于计算结果发送回数据库
commandData.AsyncResult = commandData.Command.BeginExecuteNonQuery（）;
commandDatas.Add（commandData）;
}
}
dataReader.Close（）;
WaitHandle.WaitAll的（commandDatas.Select（C =＆GT; c.AsyncResult.AsyncWaitHandle）.ToArray（））;
的foreach（在commandDatas VAR commandData）
{
commandData.Command.EndExecuteNonQuery（commandData.AsyncResult）;
commandData.Command.Dispose（）;
}
//省略 - 更多code。使用相同的SqlConnection做最后的工作
的Connection.close（）;
}
}
那么，在接收了大量的选票下来我对这个评论的极端风险。首先，这是一个很好的问题，同时指出，解决你提到的具体潜在的问题。然而，你却忽视讨论这个漫长的过程，你所要完成。
我的经验，以为我一件事......
如果这个问题你问的是很难回答，改变的问题。
虽然我知道很少您的具体问题，我认为这整齐地适用于您的predicament。正如其他人所说...临时表是肮脏的，创建自己的表一个特定的任务是令人讨厌的是，更新大量数据的SQL是昂贵的。
问问自己，你能避免这一切？
很多时候，人们选择实现在数据库中非常复杂的逻辑，因为他们有一个信念，即SQL可以做得更快。实际上，这是一个有缺陷的概念，数据库是存储/串行化设备，他们擅长存储，更新，定位和同步访问数据。他们根本没有能力处理复杂的操作。即使在微软（和其他人）通过注入完整的开发语言到它的数据库私生子，它不能执行的最佳作为一个写得很好的客户端（*视操作，我怀疑你已经超过了复杂性）。
举个例子，你有原始数据大约2GB价值的数据库。你想制造一个复杂的报表或分析整个数据集。那么内存干脆把2GB是来之不易，使用字典思乐普整个数据库（或部分需要）到内存或其他创建的外观起坐，你将需要。根据几个因素，整个事情很可能会跑的比SQL快好几倍，可以很容易地进行单元测试，并将于（恕我直言）显著更容易建立，调试和比的存储过程构建动态SQL的一个讨厌的分类维护。甚至超过2GB的原始数据，客户端高速缓存可以很容易地与现有的几种技术（B-树，ISAM等）创建的。
我的工作目前该产品已在数据库中的数据2.4TB，我们有没有一个单一的存储过程，连接语句，甚至是不相等的where子句。
但可惜我的建议可能会或可能不会有关您的具体情况，因为我不知道自己的目标或约束。我们希望，如果不出意外，这会让你问自己：
我是问正确的问题吗？
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();
}
}
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?"
这篇关于使用相同的SqlConnection多个并发调用SqlCommand.BeginExecuteNonQuery的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
