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

This article mainly introduces how to use Ado.Net Sybase, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.

A few days ago, my colleague asked me a question, a program of CS architecture, which directly takes SQL Server as a server, and each client connects directly to the database operation (kay Note: S2 cs project is this architecture). If the number of SQL Server connections opened by the client is too many, the number of SQL Server connections will be very high, and the database will form a performance bottleneck. 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 the problem of connection pooling, I have come up with two solutions to this problem:

1. Turn off connection pooling for ADO.NET, 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.

The following is an article about ADO.NET connection pooling found on the Internet. It feels good.

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.

How to implement connection pooling

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.

Advantages

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.

Thank you for reading this article carefully. I hope the article "how to use Ado.Net Sybase" shared by the editor will be helpful to everyone. At the same time, I also hope that you will support and pay attention to the industry information channel. More related knowledge is waiting for you 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.