...An APPLICATION communicates to a DATABASE via a connection PERFORMANCE is often a key concern and should influence the architecture of the systemy of a system which interfaces with a database.
Creating a new connection between an application and a database for each request poses a performance hit and having only one connection for all requests has the potential of becoming a bottleneck.
In today's data-driven environment, applications usually have a database backend that the user application connects to in order to retrieve useful information. However, creating a new connection takes between 1-3 seconds to set up the context, allocate resources, and authenticate users. For short queries, this overhead can unnecessarily dominate the transaction time. Businesses who must maintain high performance systems should attempt to eliminate the dominating bottlenecks. Using a connection pool where requests borrow a persistent connection from the pool amortizes the connection startup cost across all requests.
Imagine a busy Friday night at the pizza parlor. It would be ridiculous to have the manager schedule a new pizza delivery person for each delivery because of the startup cost to call an employee into work and supply resources. Instead, the manager has a group of people working the shift and when an order is ready, she sends out an available delivery person to deliver the pizza. When he returns, the manager may send him out again on a new delivery if there is an order waiting.
Returning to the software front, the application now interacts with a central manager (pizza manager) who manages the connection pool (group of pizza delivery people). The manager creates the initial set of connections, hands them out upon request, and collects them on return. To efficiently handle the pizza orders, there should be enough pizza delivery people to make the average delivery wait minimal. On the other hand, if too many delivery people sit idle, money is wasted. Similarly, the number of connections managed by the connection manager should correlate with the number of database requests per unit time in order to use resources efficiently. The application designer can determine this range through analysis of the system load. Just as the pizza manager must ensure that each delivery person is properly performing his job, the connection manager should monitor the healthiness of each connection and remove connections which become corrupt. When the application is shutdown, the connection manager closes each connection (manager tells the boys to finish closing shop and head home).
Therefore:
To improve performance in a system based on database requests, use a database connection pool with connection manager to gain the benefits of persistent connections.
