Optimizing database operations with object pooling

When it comes to object pooling, everyone is not unfamiliar. Many people have realized that the code on the Internet is also flying. When it comes to connection pool, who is ungrateful, who is not Xiao. Many people have implemented connection pooling themselves, trying to optimize data access. Summed up a bit, the more common ideas are as follows:

1. The opening of the database connection is more resource-intensive, and it can improve efficiency if the switch can be avoided repeatedly.

2. If a database connection is opened until the program ends, the life ends. Opening and reusing for a long time will cause the connection object to be unstable.

3. There is no guarantee that the open connection will be a problem.

4. Automate the management of database connections to solve problems caused by frequent creation, distribution, and release

In my opinion, however, these concerns are relatively not paramount. Since Microsoft has recommended a disconnect mode under the. NET Framework platform, according to Microsoft's style, there must be an appropriate solution. First ado.net itself realizes the connection pool, automatically manages the database connection life cycle (although if does not control, opens the connection randomly, also will have the pool to fill the time), second ado.net connection pool also solves the database connection repeatedly to open the efficiency question. I think: Although the common connection pool is to control the number of instances of the connection target, but the elements in the pool is actually shared, and can not control the number of concurrent operations, and does not necessarily have the ability to bring about what the essential improvement. There is something more important in database operations: the number of concurrent operands that control the same time.

First, look at the way ado.net works. Ado.net in the data access to provide 2 ways: 1, the connection mode: The general use of DbDataReader to read the data, read the process, the database connection is always open. Open the database connection before reading the data. Closes the database connection after the read is finished. 2. Disconnect mode: Use DbDataAdapter to remove the data at once, fill it in the dataset, and then close the database connection, the data in the dataset and the database are completely disconnected. By Dbdataadpater before the data is fetched, determine if the database connection is open, and if it is turned off, open the connection, fill the dataset, and then close the database connection. If the connection is turned on before fetching the data, the connection is not opened and the connection is not closed after the dataset is populated. So ado.net can be summed up in the following process: 1, open the connection. 2. Execute SQL statement to return results. 3, close the connection. It can be said that these 3 steps constitute the standard mode of data manipulation under. Net. Although data manipulation is just repeating these 3 basic steps, the details of implementation are different in single-threaded and multi-threaded applications. The common practice for single-threaded application development is for the entire application to share a database connection, which is feasible because the user can only perform database operations at the same time in one application. For multi-threaded application development, because it is multithreaded concurrency operations database, so each database operation instantiate a database connection, and then perform the standard 3 steps.

It is not difficult to see from the way the ado.net works, if the simultaneous operation of multiple data, sharing a database connection, is absolutely not allowed. Although Ado.net provides Mars (that is, multiple Active result Sets) To solve this problem, add "multipleactiveresultsets=true" to the database connection string. But not all databases support Mars, and a common SQL server2000 database is an example, and enabling Mars can significantly degrade system performance. Therefore, when the database does not support Mars, or for performance reasons, or not read but write operations, the most common practice is to instantiate a database connection for each data operation.