- Clean up your database calls Make sure that you close your ResultSet and the database connection after every call to the database or you risk to fill-up your connection pool with dozens of active unused connections. When working with only a single connection it's easy to conveniently "forget" to close a ResultSet or a connection, so the next method can just re-use the same resource. In a pool, any connection could potentially be a newly allocated resource which you might have to initialize.
- Optimize and minimize your SQL calls Use specialized queries when appropriate. Make sure that you don't initialize an entire object if you only need a handful of fields for a given function. Initializing an entire object might require a number of SQL calls, which can be minimized with a single specialized query. Avoid SQL calls that retrieve large lists of data, use limits whenever possible.
- Don't expect a connection pool to solve all your database performance problems A connection pool is not a magic pill and is not always the best design decision. In most cases an application with well designed and optimized SQL calls will work just fine without a connection pool. In fact a database connection pools introduce their own operational limits (such as the maximum connections allowed) and add complexity to your application. On a positive side, implementing a connection pool will help you identify and expose any short falls in your exiting database design.
- Consider using existing open source database connection pool project Although implementing your own connection pool object is not rocket science, you should consider using an open source project if you can. Most open source connection projects will provide you with dozens of fine-tune settings to optimize your pool. When implementing your own pool, it will be difficult to consider all the settings that might be required to fine-tune your pool. Also most open source pool projects will provide you nice GUI widgets to show the status of the pool within your own application. We used Proxool http://proxool.sourceforge.net/index.html because of the comprehensive support for web applications built-in to Proxool.
- Don't just assume that it works ... Load Test! Once your pool is implemented, make sure that you test your application under a heavy load. Watch how your pool behaves and try to determine some benchmarks. Excessive number of active connections in your pool during load testing will tell you that you need to improve your SQL calls. Make sure that the number of active connections returns to the minimum after each load test. In conclusion, database connection pool can be useful in some applications but should not be treated as a must have requirement in every situation. Just like any other feature in your application, implementing a database connection pool should be approached with a proper design and implementation plan.