Shulou(Shulou.com)06/02 Report--

This article introduces the relevant knowledge of "how to solve the ADO.NET connection pool problem". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

A program based on CS architecture, which directly takes SQL Server as the server, and each client connects to the database directly. If the number of SQL Server connections opened by the client is too large, the number of connections to the database will be particularly high, and the database will form a performance bottleneck. What to do in this case? On second thought, the reason for this is the internal mechanism of ADO.NET connection pooling.

Connection pooling is used in ADO.NET to improve performance, so that each request does not have to create a connection, then authenticate, and then execute SQL,ADO.NET connection pooling. Instead of taking the connection out of the connection pool and performing SQL, the connection is not actually closed after execution, but is put back into the connection pool. If there are 100 clients, and each client saves 10 connections in the connection pool after using it for a period of time, then in this case, even if nothing is done on the client, there are 1000 connections on the SQL Server, so it is not surprising that there are no performance problems.

Since it is a problem of connection pooling, there are two solutions to this problem:

1. Close ADO.NET connection pooling, create a new connection each time SQL is executed, and then close. Doing so will slow down the data query (establish a connection every time, authenticate every time, of course), but this slow is millisecond and generally imperceptible, but it may be felt in detail if hundreds of SQL statements are involved in an operation. The method of modification is very simple, you don't have to modify the code, just add Pooling=False; to the database link string.

two。 Modify the architecture, this CS architecture in addition to performance problems, such as security problems. You can change the method of directly connecting to the database to connecting to the service, in which you can use Remoting, Web services, and so on, of course, you can now use WCF. In this way, only the server connects to the database, while the client only connects to the server, so that there is no bottleneck caused by connection pooling. However, there are a lot of code changes in this way, and it is still very painful to change it.

Introduce ADO.NET connection pooling

Connection pooling allows an application to obtain a connection from a connection pool and use that connection without having to establish a new connection for each connection request. Once a new connection is created and placed in the connection pool, the application can reuse the connection without having to implement the entire database connection creation process.

When an application requests a connection, the connection pool allocates a connection to the application instead of re-establishing a connection; when the application finishes using the connection, the connection is returned to the connection pool rather than released directly. Make sure you use the same connection string each time (the same as the connection pool); the connection pool works only if the connection string is the same. If the connection string is different, the ADO.NET connection pooling application does not use the connection pool but creates a new connection.

This is the end of the content of "how to solve the ADO.NET connection Pool problem". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.