• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Proper way to use a connection pool

 
Jay Damon
Ranch Hand
Posts: 282
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What is the proper way to use a database connection pool? My understanding is that, when I wish to execute an SQL statement, I should 1) request a Connection from the pool, 2) execute the SQL, and 3) return the Connection to the pool.

Some of my fellow developers, however, believe that when a user request is received, a Connection should be obtained at the beginning of the request and held onto until the request is completed.

I understand that, if I obtain connections as described in the first paragraph, I may have to request multiple connections during a single request. However, I believe that obtaining a single Connection for the duration of a request is wasteful, particularly for our poorly written application, which often takes several seconds to complete a single page request.

Comments please.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Jay,
You are correct. The connection pool is responsible for managing the connections. And it can't do its job if developers don't return the connections as soon as possible.

The overhead of returning a connection to the pool is extremely low. So there is no harm in doing that. By comparison, not returning connections to the pool diminishes the number of connections available to other clients.
 
Sri Ram
Ranch Hand
Posts: 118
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I think there are a lots of questions related to Connection Pooling floating about. I want to know whether there is any write up on this. I have googled and found only how to Create a connection pool. . But never have found how to get connection from a Pool and when to release it, Whats the best and worst practice, and things like that.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic