Connection pooling: is it appropriate

I'm building a Java based web app (primarily JSPs deployed with Tomcat). The number of users will never be more than ~30 people. It is a work log, so the users will constantly be updat开发者_开发技巧ing/accessing the database (SQL Server). There are many methods in the web app that require a connection to the database.

I open a new connection each time one is required (I also close it appropriately), but this seems like a lot of opening/closing of connections. Does connection pooling apply to this situation? I'm trying to understand the role of the pool, but I'm confused; would there be a connection pool for each user?

If I'm way off track (which I have a suspicion I am), then is there a better solution to this problem? Is there even a problem?

Thanks!

---

Yes, I'd size the pool for 30 connections and let it manage them. You'll amortize the cost of opening connections over all requests that way.

There's one pool that many users would access to get connections; one connection per request.

---

The connection pool is for the application (not per user). The concept of connection pool is to reuse open connections as much as possible and open a new one when it is absolutely needed. Opening a connection to a database is an expensive operation in terms of both cpu cycles and memory. Thats why a connection pool is needed. For 30 users, i would recommend using a connection pool.

You can size the pool anywhere between 15 to 30 connections in the pool.

take a look at http://commons.apache.org/dbcp/

---

You certainly could pool connections to the database. Generally you'd use one pool per DB (though there could be reasons that you'd have more).

You're right to ask whether there's even a problem. Connection pooling is going to reduce the number of new connections that have to be negotiated, so it will reduce the time it takes to service a request and also reduce load on the servers. Also it will reduce the number of sockets used, which (for larger applications) can be a factor in system performance.

However: do you have a performance problem that you're trying to solve? Are response times acceptable? Is load acceptable? Balance what you'd gain in perf versus development cost. Pre-built connection pools exist, so it's likely easy to integrate one. But it's not free and optimization should generally be done with specific goals, not "because I should".

---

The point here is less the number of users, but the number for requests that require opening a connection.

If you have

 for (int i = 0; i < 1000 ; i ++ ) { 
       Connection c = getConnection();
       dosomwthingWith(c);
       c.close();
 }

You will still benefit from the connection pool, as the c.close() is not really closing the connection, but just putting it back into the pool.