SQL连接打开速度变慢或使应用程序性能更好 [英] Sql Connection open slow down or make application performance better

问题描述

我正在查询表以查找具有应用程序中的5,000条记录的特定记录.每当我查询

I am querying to a table for specific records having 5,00,000 records in application. When ever i query

例如:

...........  Some Code

for i = 0 ;  i < 60,000

.............  Some code

connection.Open();

select * from ERet where [Key] = Variable+i ;

connection.Close();
--------------some calculation

.............some code

next

此connection.open/close是否会影响应用程序性能?我是否需要连接才能在循环前打开并在循环后关闭以提高应用程序性能.

Should this connection.open/close will affect the application performance? Should i need connection to open before the loop and close after loop for the better application performance.

推荐答案

在这种特殊情况下，因为您的循环太紧了(嗯，所以可能不那么紧，但是您肯定在执行在该范围内进行大量操作)，则应在进入循环之前将连接保持在循环外部打开，然后确保在完成循环后将其关闭.例如:

In this particular case, because you have a tight loop (well, it might not be so tight, but you definitely are performing a large amount of operations in that scope), you should keep the connection open outside of the loop before you enter the loop, and then make sure to close it when the loop is done. For example:

using (var connection = new SqlConnection("connection string"))
foreach (...)
{
   // Do your work here.
}

尽管连接可能被回收或不被回收/池化(取决于您的设置)，但是在池连接中仍然存在一些开销(从池中拉出它们时需要重置它们)，然后执行任何操作 60,000次将产生 some 开销；您不妨在可能的地方和知道的地方将其取出来，不会对您造成负面影响.

While connections might or might not be recycled/pooled (depending on your settings), there is still some overhead in pooling connections (they need to be reset when you pull them from a pool), and doing anything 60,000 times is going to have some overhead; you might as well take it out where you can and where you know it won't impact you negatively.

此外，正如 Mitch Wheat 在

Also, as Mitch Wheat points out in his answer, and important question to ask is whether or not you have to perform 60,000 queries; it would appear from your code you are performing the same exact query over and over, when just once might suffice, or you might be able to collect the conditions you need to query on into one query and then process your data.

这篇关于SQL连接打开速度变慢或使应用程序性能更好的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！