How important is it for me to unite my ties?

Question

I was asked to change my code to "merge" my ADO connections. On each web page, I will open one connection and continue to use the same open connection. But someone told me that it was important 10 years ago, but now it is not so important. If I make, say, 5 dB of calls in a web publication, is it difficult to use 5 separate connections that I open / close?

Answer 1

Connections to SQL Server are automatically aggregated into an ASP.NET application: one pool for each individual connection string. If you follow the best practices and hide your database code in the DAL, the connection string of which is constant throughout the application, you will always work with one pool of connection objects.

So what does this mean for your approach to the database? Well, for one, this means that “closing the connection” really translates as “returning the connection to the pool”, and not really closing the application link to SQL Server. Thus, closing and reopening are not so important for the deal. However, with that said, there are several best practices.

First, you don’t want connections to end in your pool, even if your application scales dramatically. That is, never think in turn "user on this page" - think in terms of "thousands of people using this page."

Secondly, although it’s not the case to charge for closing and reopening the connection, you usually want to open the connection as late , using it until it is completed, and then close it as early as possible . The only exception is if you have a time-consuming process that must appear after some data is extracted and before other data is saved or received.

Thirdly, I would strongly advise you not to open the connection at the beginning of the page life cycle and close it at the end of the life cycle by another method. What for? Because the worst thing you can do is leave the connection open, because you forgot to add logic to close it. Yes, they will eventually be closed when the GC starts to work, but again, if you are thinking of “thousands of people using this page”, the likelihood of real problems becoming obvious.

Now, if you say that you are sure that you close the connection, because you always do it in some key logical place (for example, the Page_Unload method). Well, that’s great as long as you can confidently say that you will never throw an error that jumps out of the page life cycle. Which you cannot. Therefore ... do not open pages in one method of the life cycle and do not close in another.

Finally, I highly recommend implementing DAL, which manages database connections and provides tools for working with data. In addition, create a Business Logic Layer (BLL) that uses these tools to provide security type objects to the user interface (for example, Model objects). If you implement BLL objects with the IDisposable interface, you can always ensure the security of the connection using the scope. It will also allow you to keep the database connection open for a very short period of time: just open the BLL object, drag the data to a local object or list, and then close the BLL object (exit the scope). Then you can work with the data returned by the BLL after the connection has been closed.

So ... what it looks like. Well, on your pages (user interface) you will use the Business Logic classes as follows:

using (BusinessLogicSubClass bLogic = new BusinessLogicSubClass()) { // Retrieve and display data or pull from your UI and update // using methods built into the bLogic object . . . } // <-- Going out of scope will automatically call the dispose method and close the database connection. 

Your BusinessLogicSubClass will be obtained from the BusinessLogic object that implements IDisposable. It will instantiate your DAL class and open the connection as follows:

 public class BusinessLogic : IDisposable { protected DBManagementClass qry; public BusinessLogic() { qry = new DBManagementClass(); } public void Dispose() { qry.Dispose(); <-- qry does the connection management as described below. } ... other methods that work with the qry class to ... retrieve, manipulate, update, etc. the data ... Example: returning a List<ModelClass> to the UI ... } 

when the BusinessLogic class goes out of scope, the Dispose method will be called automatically because it implements the IDisposable interface.

Your DAL class will have the appropriate methods:

 public class DBManagementClass : IDisposable { public static string ConnectionString { get; set; } // ConnectionString is initialized when the App starts up. public DBManagementClass() { conn = new SqlConnection(ConnectionString); conn.Open(); } public void Dispose() { conn.Close(); } ... other methods } 

Given what I have described so far, DAL does not have to be IDisposable. However, I use my DAL class extensively in my test code when I test new BLL methods, so I built the DAL as IDisposable so that I can use the using construct during testing.

Follow this approach and, in essence, you will no longer have to think about a connection pool.

Answer 2

If you use ADO.NET with SQL Server, your connections are most likely already merged. Connection pooling is a very common database practice these days, and most of the time you use a join without knowing it. I suppose you were told that manual joining is not that important since it is usually automatic.

This is good practice for several reasons. First, it takes a certain amount of time to create a connection, and constantly closing and opening your connections can waste valuable time. Secondly, connections are often the end resource and should not be wasted. Often, stack level restrictions can also hinder how often connections can be opened. In high-performance environments, actually opening and closing connections can use the end resource and create a bottleneck as they gradually become available again.

Answer 3

From ASP.NET 2.0 framework, the default connection pool for SQL Server.

However, what the people you talked about is not really coming together. Rather, it is about reducing the number of database sessions.

When the connections are combined, the penalty for closing the connection object and opening another is quite small. What usually happens is that the database connection is returned to the connection pool, and when you create the next connection object, it simply reestablishes the database connection using the same connection that you just returned.

However, since each time the connection is restored, a database query is made, you should try to reduce the number of connected objects that you use, if possible.

The page loop makes it difficult to save the connection object for all operations with the database, so I usually use one connection object to regularly select the data executed in Page_Load, then another connection, if necessary for the example in the button event for updating the data in the database.

Answer 4

I would say that it is a good idea to combine connections to everything. Almost everything has a finite connection limit. In addition, there is overhead associated with opening connections, so using the same connection is much more efficient and saves response time.

What if you scale to 15 database queries? These compounds begin to accumulate.

Pool. There is no reason for this.

Of course, servers can chew something these days, but any response time that you can save improves the user experience.

Answer 5

ADO.NET will often combine your connections. This is sometimes considered a good thing; he is trying to do the right thing and probably will not cause grief.

You do not need to do anything special to achieve this, except for creating connections with the same parameters each time (this is not difficult, just use the same procedure to create connections on each page).

Of course, joined connections can lead to difficulties in unusual cases, in which case you can disable them. But otherwise, just leave it alone and it should work.

Answer 6

The key thing I found is the time it takes to create a database connection.

For a MSSQL Server in a slow connection halfway around the world, the union will noticeably improve your application response.

For a local MySQL installation, you may not notice a significant difference.