What is the benefit of Connection and Statement Pooling ?

Question

Can someone explain what is Connection and Statement Pooling and what is the benefit over unpooled DataSources? I am trying to understand when it is a good idea to use a technology like c3p0 or proxool in a project. I need first to understand what they do and when it interesting to use them. Thank you very much.

Answer 1

Connecting and disconnecting from a database is an expensive operation. By using pooling you can write your code to open and close connections but the pool decides when to actually do it, leaving a certain number of connections open for a certain time.

Statement pooling? Are you talking about statement caching?

Answer 2

Creating a network connection to a database server is (relatively) expensive. Likewise asking the server to prepare a SQL statement is (relatively) expensive.

Using a connection/statement pool, you can reuse existing connections/prepared statements, avoiding the cost of initiating a connection, parsing SQL etc.

Answer 3

I am not familiar with c3p0, but the benefits of pooling connections and statements include:

1. Performance. Connecting to the database is expensive and slow. Pooled connections can be left physically connected to the database, and shared amongst the various components that need database access. That way the connection cost is paid for once and amortized across all the consuming components.

2. Diagnostics. If you have one sub-system responsible for connecting to the database, it becomes easier to diagnose and analyze database connection usage.

3. Maintainability. Again, if you have one sub-system responsible for handing out database connections, your code will be easier to maintain than if each component connected to the database itself.

HTH, Kent

Answer 4

Quoting the book JAVA Persistance with Hibernate

There are three reasons for using a pool:

• Acquiring a new connection is expensive. Some database management systems even start a completely new server process for each connection.

• Maintaining many idle connections is expensive for a database management system, and the pool can optimize the usage of idle connections (or disconnect if there are no requests).

• Creating prepared statements is also expensive for some drivers, and the connection pool can cache statements for a connections across requests.