多次打开和关闭SQL连接有多严重?确切的作用是什么? [英] How bad is opening and closing a SQL connection for several times? What is the exact effect?

问题描述

例如，我需要使用SQLDataAdapter的Fill()方法填充大量数据表:

For example, I need to fill lots of DataTables with SQLDataAdapter's Fill() method:

DataAdapter1.Fill(DataTable1);
DataAdapter2.Fill(DataTable2);
DataAdapter3.Fill(DataTable3);
DataAdapter4.Fill(DataTable4);
DataAdapter5.Fill(DataTable5);
....
....

即使所有dataadapter对象都使用相同的SQLConnection，除非在方法调用之前连接状态已经打开，否则每个Fill方法都将打开和关闭连接.

Even all the dataadapter objects use the same SQLConnection, each Fill method will open and close the connection unless the connection state is already open before the method call.

我想知道的是不必要地打开和关闭SQLConnections如何影响应用程序的性能.它需要扩展多少才能看到此问题的负面影响(100,000个并发用户?).在一个中等规模的网站(每天有50000个用户)中，是否值得打扰并搜索所有Fill()调用，将它们保存在代码中，并在任何Fill()调用之前打开连接，然后关闭?

What I want to know is how does unnecessarily opening and closing SQLConnections affect the performance of the application. How much does it need to scale to see the bad effects of this problem (100,000s of concurrent users?). In a mid-size website (daily 50000 users) does it worth bothering and searching for all the Fill() calls, keeping them together in the code and opening the connection before any Fill() call and closing afterwards?

推荐答案

ADO.NET具有连接池，例如当您关闭连接时，它并没有真正完全关闭，但是如果您要求使用完全相同的连接字符串进行新连接，则会回收".

ADO.NET has connection pooling, e.g. when you close a connection, it's not really completely closed, but "recycled" if you ask for a new connection with exactly the same connection string.

尽管如此-如果您已经提前知道，则必须一一调用这五个Fill方法，我绝对会推荐

Nevertheless - if you already know ahead of time that you'll have to call these five Fill methods one by one, I would definitely recommend

• 打开连接
• 从数据库中读取所有五个数据表
• 立即关闭连接

以这种方式进行操作是公认的最佳实践，它不会对您造成伤害-因此，只需执行此操作即可！ :-)

It's accepted best practice to do it this way, it doesn't hurt you - so just do it! :-)

马克

PS:仅在您尚未关闭ADO.NET的情况下，连接池才有效！ :-)默认情况下处于启用状态-您必须明确禁用它.

PS: Connection pooling in ADO.NET of course only works if you haven't turned it off ! :-) It's on by default - you'd have to explicitly disable it.

这篇关于多次打开和关闭SQL连接有多严重?确切的作用是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！