Having recently implemented a database connection pooling in our web applications, I want to share some of the issues we stumbled upon during the implementation.

• 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.

Tags: connection, database, pool, sql

Welcome to FileCatalyst’s new blog.  Check back regularly as we open up about the FileCatalyst family of products, with discussions about fast file transfer, managed file transfer, and how technologies like FTP can be replaced. All the while, keep up with the goings-on at FileCatalyst’s parent company, Unlimi-Tech Software, Inc. and the people who make FileCatalyst what it is.

We have all kinds of useful articles planned, including practical FileCatalyst-related subjects like:

• Deploying FileCatalyst Products
• Getting the most out of your configuration
• Common problems and how to solve them (aka, “My Firewall is giving me grief!”)
• Developing a web application using FileCatalyst applets
• General tutorials in maximizing your FileCatalyst experience

We’ll also be talking about the state of file transfer, answering questions such as:

• How can FTP alternatives be “100X faster than FTP”?
• How can I get more performance without changing my existing infrastructure?
• Why do I need managed file transfer?
• What is it about TCP that can make it so inefficient?

And with any luck, we’ll also get around to general chat from the personalities working here. Peek into the development of the world’s finest file transfer software. Learn about the challenges we face in this specialized industry. Rally behind us as we cheer for the Ottawa Senators NHL hockey team.

Learn a thing or two about FileCatalyst products, a lot about file transfer, and a smidge about who we are. All coming soon to this space!