Database Tutorial – Single Connection vs Connection Pool

In the previous article from the Best Database Practices series I explained the importance of using PreparedStatements. I also stated that using PreparedStatements in connection pool might be tricky. Today, I will explain why.

You already know what a connection is. The main characteristic of a single database connection is that you can only use the one connection for all your queries. This means that your whole application will always use the shared connection to execute many queries triggered by many users. As you probably guessed, this approach comes with many disadvantages.

Single Connection

It all depends on the type of you application. Will it be used by max 100 users at the same time and only consist of simple queries? You might use one shared database connection and if properly configured, it is very possible you will never come across any problems. But as soon as the number of queries starts to grow and queries get more complicated, the connection will start to be slow and unreliable.

Let’s discuss some of the disadvantages/risks of a one shared connection:

• You’re allowing all of your queries to run on a single instance of a connection. It’s speed is relative to the number of requests executed per second. 50 simple queries per second will not cause you any harm. 10.000 queries per second will make it extremely slow.
• Your whole application depends on one connection. If at any point the connection is closed and you do not implement a mechanism to open the connection again, you’re screwed.
• Connecting to database is a costly maneuver. If you decide to prevent connection loss by constantly closing connection of finished query and establishing a new one when new query arrives, you will find a significant decrease in your application performance.

Connection pool

It’s a very smart move to implement connection pooling, even if your application doesn’t have hundreds of active users at the same exact time. It solves all of the problems that I specified in the chapter about Single Connection. Wikipedia has a pretty decent explanation on what a connection pool is:

When you start your application, a number of connections is created and added to the pool. Their number depends on your application configuration – it’s up to you what number you choose (but be aware that more is not always better). Then, the pool starts to assign connections for different requests. After it reached its maximum number of connections at the same time (again, something you configure in your app), it waits until one of the connections is free again.

Javainsimpleway has a great picture that visualizes the logic I just explained.

Connection Pools and PreparedStatements

Don’t get me wrong – you can use PreparedStatements with connection pooling without no problem. You get a connection from data source, execute prepared statement on the connection and retrieve results. The problem arises as soon as you wan’t to re-use the same prepared statement over and over again.

Prepared statements last for a duration of a connection. With connection pooling, you prepare a statement, execute it and then return it to the pool. Although this also means that you lost your PreparedStatement, because connection is closed.

DataSource ds = new BasicDataSource();
Connection conn = ds.getConnection();
ds.setDefaultAutoCommit(false);
PreparedStatement stmt = conn.prepareStatement(sqlQuery);
... // prepare variables here
ResultSet rset = stmt.executeQuery();
conn.commit();
conn.close();

conn.close() returns the connection to the pool, which  means that your PreparedStatement is now lost.

If, in your application, you execute the same query over and over again, you need to think about either caching Prepared Statements or using PreparedStatement pooling. I’m not going to go into detail about caching prepared statements, you will have to Google this to get more information. So let’s skip to PreparedStatement pooling. The idea behind this is the same as with Connection pooling – you get multiple resources of the same type. The way it works is that when you execute method conn.prepareStatement(sqlQuery), the connection first checks if the exact same prepared statement already exists in the pool. If it does, you retrieve it and don’t have to create a new one. This helps you get better performance out of your database layer.

Conclusion

If you’re aiming for improved performance, use Connection pooling together with either PreparedStatement pooling or caching. If you’re aiming for simplicity and your application is not so complicated in the backstage, use single Connection with no pooling. It’s as easy as that.

Hope this article helped! Leave a comment if you have any questions.