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.

Terracotta

Answer 1

The Happy Connection

It's so easy to create a new connection every time. One line: that's all it takes. Nothing much to think about. Great life.

Hold on. Do you eat on a plate?

Do you throw away your plate after each use?

No, you wash it and put it on the dish rack, so you can use it again on your next meal. Buying new plates everytime is out of the question. If you did that, you will have wasted enough money to buy a new iPad in one year.

Think about connection pools again.

But this time, the connections are your plates, the connection pool is your dish rack. Your wallet and your energy represent the system resources (memory and bandwidth).

Wash or Spend?

What would you rather do:

a. wash the dishes

b. or run to the mall every meal and buy new plates?

While there are tasks involved in connection pooling, it's less taxing in the long run compared to creating new connections every time. The key is in knowing how many plates (connections) your family (application) will need in any given day.

Pools can be used for database connections, threads, entity beans and other factory-derived objects.

Patrimony

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.

Krueger

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.

Urmia

Answer 4

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?

Devilmaycare

Answer 5

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.

Cud