How bad is opening and closing a SQL connection for several times? What is the exact effect?
- by Eren
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);
....
....
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.
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?
