在循环中调用 sqlCommand 会增加每一步的执行时间
我有一个循环，它在超过 40,000 次迭代的循环中执行存储过程，如下所示：
SqlCommand command = new SqlCommand("WriteDataToDB");
command.Connection = _connection;
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@SignalID", SqlDbType.Int).Value = Arg_Signal.SignalID;
command.Parameters.Add("@SignalStrength", SqlDbType.Float).Value = Arg_Signal.SignalSiggestion;
command.Parameters.Add("@Time", SqlDbType.BigInt).Value = Arg_Signal.TimeWasHit;
command.Parameters.Add("@Value", SqlDbType.Float).Value = Arg_Signal.ValueWasHit;
if (command.Connection.State != ConnectionState.Open)
{
command.Connection.Open();
}
command.ExecuteNonQuery();
从循环中调用此代码，我在每 1000 次迭代时进行拦截和计时。我得到的时间如下：
[0]：“Started 0ms” [1]: "1000 完成 578.125ms"
[2]: "1000 完成 921.875ms"
[3]: "1000 完成 1328.125ms"
[4]: "1000 完成 1734.375ms"
[5]: "1000 完成 1140.625ms"
[6]: "1000 完成 1250ms"
[7]: "1000 完成 1703.125ms"
[8]: "1000 完成 1718.75ms"
......
[31]: "1000 完成 3234.375ms"
[32]: " 1000 完成 3390.625ms"
[33]: "1000 完成 3453.125ms"
[34]: "1000 完成 3609.375ms"
[35]: "1000 完成 3765.625ms"
[36]: "1000 完成 3796.875ms"
[37]: " 1000 完成 3968.75ms"
[38]: "1000 完成 4093.75ms"
[39]: "1000 完成 4203.125ms"
[40]: "1000 完成 4546.875ms"
[41]: "1000 完成 4406.25ms"
[42]: "已停止，总计 101093.75ms 1515.625ms”
有谁知道为什么这些执行时间在增加？我需要运行这段代码超过一百万次迭代 - 按照其运行速度，执行一次迭代将需要一分钟......
非常感谢
I've got a loop that executes the stored procedure in a loop with over 40,000 iterations, like so:
SqlCommand command = new SqlCommand("WriteDataToDB");
command.Connection = _connection;
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@SignalID", SqlDbType.Int).Value = Arg_Signal.SignalID;
command.Parameters.Add("@SignalStrength", SqlDbType.Float).Value = Arg_Signal.SignalSiggestion;
command.Parameters.Add("@Time", SqlDbType.BigInt).Value = Arg_Signal.TimeWasHit;
command.Parameters.Add("@Value", SqlDbType.Float).Value = Arg_Signal.ValueWasHit;
if (command.Connection.State != ConnectionState.Open)
{
command.Connection.Open();
}
command.ExecuteNonQuery();
This code is called from a loop where I intercept and time every 1000th iteration. The times I get are below:
[0]: "Started 0ms"
[1]: "1000 done 578.125ms"
[2]: "1000 done 921.875ms"
[3]: "1000 done 1328.125ms"
[4]: "1000 done 1734.375ms"
[5]: "1000 done 1140.625ms"
[6]: "1000 done 1250ms"
[7]: "1000 done 1703.125ms"
[8]: "1000 done 1718.75ms"
......
[31]: "1000 done 3234.375ms"
[32]: "1000 done 3390.625ms"
[33]: "1000 done 3453.125ms"
[34]: "1000 done 3609.375ms"
[35]: "1000 done 3765.625ms"
[36]: "1000 done 3796.875ms"
[37]: "1000 done 3968.75ms"
[38]: "1000 done 4093.75ms"
[39]: "1000 done 4203.125ms"
[40]: "1000 done 4546.875ms"
[41]: "1000 done 4406.25ms"
[42]: "Stopped with total 101093.75ms 1515.625ms"
Does anyone have an idea why these execution times are increasing? I need to run this code with over a million iterations - by the rate its going its going to take a minute to execute one iteration...
Many thanks
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您的存储过程中是否有任何特殊逻辑，或者您只是插入到表中。
如果没有特殊逻辑或者您可以在 .NET 中执行该逻辑，请查看执行 批量插入。您可以利用 System.Data.SqlClient.SqlBulkCopy 类来完成此操作。
Is there any special logic in your stored proc or are you just inserting into a table.
If there is no special logic or you can do that logic in .NET then have a look at performing a Bulk Insert. You can do this by utilising the
System.Data.SqlClient.SqlBulkCopyclass.我不能说我知道为什么你每次都会变慢（听起来你实际上并不是每次都清除“1000”，而是实际上附加它们或其他东西）但是如果你想将数据转储到数据库中你应该使用类似 SqlBulkCopy 的东西，而不是for 循环内的存储过程。
I can't say I know why you it goes slower each time (it would sound like you are not actually clearing the "1000" each time, but actually appending them or something) but if you are looking to dump data into a database you should be using something like SqlBulkCopy, not a stored proc inside a for loop.
这只是一个猜测，但您会在每次迭代中添加更多参数。是的，它们的名称与以前相同，但我不知道
SqlCommand类是否足够聪明来处理这个问题。尝试添加参数一次，然后在循环中设置它们的值，即。循环外：
循环内：
如果这没有帮助，您必须分析代码并查看速度慢的地方 - 它可能不是实际的数据库调用。
顺便说一句，您还可以尝试在多次执行同一命令时调用
command.Prepare()- 它不会解决这个特定问题，但无论如何可能会产生轻微的性能差异。This is just a guess, but you're adding more parameters on each iteration. Yes, they have the same names as before, but I don't know if the
SqlCommandclass is clever enough to handle that or not. Try adding the parameters once and then just setting their value in the loop, ie.Outside of the loop:
Inside the loop:
If that doesn't help you'd have to profile the code and see where it's slow - it may not be the actual DB call.
As an aside, you could also try calling
command.Prepare()when executing the same command many times - it wouldn't fix this particular problem, but may just make a slight performance difference for the better anyway.