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

This article mainly explains "what are the shortcomings of ADO.NET database". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Next let the editor to take you to learn "what are the shortcomings of ADO.NET database?"

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? After thinking about it, the reason for this is the internal mechanism of ADO.NET.

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, but directly removes the connection from the connection pool and executes SQL. After execution, the connection is not actually closed, but 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. Turn off connection pooling for ADO.NET database connections, 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.

Change 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 database 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 application does not use the connection pool but creates a new connection.

The main advantage of using connection pooling is performance. The time it takes to create a new database connection depends largely on the speed of the network and the distance between the application and the database server, and this process is usually a time-consuming process. After using the database connection pool, the database connection request can be satisfied directly through the connection pool without the need to reconnect and authenticate to the database server for the request, which saves time.

Shortcoming

There may be multiple unused connections to the database in the ADO.NET database connection pool (which means a waste of resources).

1. Create a connection pool when you need a database connection, not in advance. Close the connection as soon as you finish using it, and don't wait for the garbage collector to deal with it.

two。 Ensure that all user-defined transactions are closed before closing the database connection.

3. Do not close all connections in the database and ensure that at least one connection is available in the connection pool. If memory and other resources are your first consideration, you can close all connections and create a connection pool when the next request arrives.

At this point, I believe you have a deeper understanding of "what are the shortcomings of the ADO.NET database?" you might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.