6

I can't understand why connection pooling improves application performance.

I suppose connection establishment would cause latency. But reusing an established connection requires more complex error handling. Application server need track state of every connection to set connection to initial state before reusing (such as discarding all transactions, switching to the default isolation level), which also costs time.

3
  • 1
    Because re-using a database connection (even if you have to do the things you described) is still less expensive than standing up a new connection from scratch. Resetting an existing connection is probably less expensive than executing a routine SELECT statement. Jan 31, 2014 at 4:54
  • 2
  • 1
    Have you tried profiling with a simple test? Loop with 10k updates with one connection vs. 10k updates with connect/disconnects for each?
    – DXM
    Jan 31, 2014 at 5:15

3 Answers 3

5

The complex error handling is usually provided by libraries, and if your application is well-behaved, it shouldn't be needed too much anyway. The complex error handling required during attempting to create a new connection is far more likely to be invoked.

Reseting the state of the connection is trivial and inexpensive; it's built into all modern connection managers that I'm aware of.

Nothing can reduce the overheads of creating a new connection to a database.

2

Opening a new connection takes a long time - client libraries have to work out which server to talk to, then open a negotiation with that server to get an initial connection, then start bouncing credentials back and forth to authenticate the client, then start up a database session, and [finally] configure that session the way the application wants it.
Slow!

Pooling connections allows most (but not all) of that overhead to be eliminated with no extra work on the part of the application. The bulk of your error handling is around not being able to get into the database in the first place.

Great for web-applications, where the web server has to get in, do something and get out again, over and over again for a multitude of difference clients.

Not so good for, say, a Windows Service that's running "in background" 24x7 but where the underlying database "disappears" for "some time" every night for housekeeping and other, maintenance activities.
Sure, you could shut the service down while that's going on, but (in the absence of any managed way of doing so at the time) our solution was to build the application to treat its database as a transient entity. It established a database connection when it could and then held it open and, whenever it wanted to use the database, half-expected it to have "disappeared". And yes, the error handling needed was far more complicated, but that service has now been running quite happily for a decade and half! (OK, not continuously; the host gets rebooted every month for patching).

1
  • 2
    What about prepared statement caches stored at the connection level?
    – user40980
    Jan 31, 2014 at 13:20
1

Mainly because the application server has a pool of database connections open and ready for use.

If you program had to connect and login to the database every time it would slow it down considerably.

There are other advantages such as handling all the variations in connecting to different DBMSes from different vendors, and, handling the connection configuration in a consistent and manageable way. Just imagine if you had 100 services which made thier own connection to a MySql db and your manager told you to connect to Postgress instead. With a J2EE application server this would entail changing a few entries in a single configuration file, without connection pooling you would need to make changes in 100 application programs.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.