Raghavendra
Joined: 21 Aug 2007
Posts: 222
|
 Posted: Tue Dec 29, 2009 5:43 am Post subject: Advantages of Connection Pooling in ADO.Net |
 |
|
Performance is an important consideration in modern software systems. Developers look for each and every opportunity to fine tune their applications for performance. To that end, ADO.NET connection pooling can significantly help you to write data access code that is performance oriented. Although enabled by default, one must know how to take control and fine tune the connection pooling to make the most of the feature.
What Is Connection Pooling?
Data-driven applications frequently access a database to query data. There are two broad ways to access the data:
1. Open a connection with the database as your application starts and keep it open throughout the life of your application. Fire all the queries through this open connection.
2. Open a connection just before executing a query and close it immediately once the query execution is over.
As you might have guessed, the former way is better as far as performance is concerned. However, it suffers from a serious limitation. Because one connection is held open for a large window, in terms of scalability it is extremely poor. In multiuser scenarios, such as web sites, this will restrict the total number of users who can access your application.
The second way is safe and follows the philosophy—"Open the connection as late as possible and close the connection as early as possible." Although this approach is good in terms of scalability and multiuser scenarios, it has its own disadvantage. The frequent creation, opening, closing, and destroying of the database connections results in performance penalty. This is precisely where database connection pooling come handy.
A database connection pool is a set of database connections that are held open with the database and are kept ready to serve. This way, a set of connections is created once the pool comes into existence. When a user requests a connection, an already created connection is served from the pool. Similarly, when a user closes a connection, it is returned to the pool instead of being destroyed. This can significantly improve the performance of data access.
Have a look at the following article. It will give the explanation with good example and sample code.
http://www.developer.com/db/article.php/3729831/Using-Connection-Pooling-in-ADONET.htm
Thanks & Regards,
Raghavendra K. |
|
