JDBC Connection pooling for SQL Server: DBCP vs C3P0 vs No Pooling

Question

I got this Java webapp which happens to communicate too much with a SQL Server Database. I wanna decide how to manage the connections to this DB in an efficient manner. The first option which pops to mind is using connection pooling third parties. I chose C3P0 and DBCP and prepared some test cases to compare these approaches as follows:

No Pooling:

public static void main(String[] args) {
        long startTime=System.currentTimeMillis();
        try {
            for (int i = 0; i < 100; i++) {
                Connection conn = ConnectionManager_SQL.getInstance().getConnection();

                String query = "SELECT * FROM MyTable;";
                PreparedStatement prest = conn.prepareStatement(query);

                ResultSet rs = prest.executeQuery();
                if (rs.next()) {
                    System.out.println(i + ": " + rs.getString("CorpName"));
                }
                conn.close();
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        System.out.println("Finished in: "+(System.currentTimeMillis()-startTime)+" milli secs");
    }

DBCP:

public static void main(String[] args) {
        long startTime=System.currentTimeMillis();
        try {
            for (int i = 0; i < 100; i++) {
                Connection conn = ConnectionManager_SQL_DBCP.getInstance().getConnection();

                String query = "SELECT * FROM MyTable;";
                PreparedStatement prest = conn.prepareStatement(query);

                ResultSet rs = prest.executeQuery();
                if (rs.next()) {
                    System.out.println(i + ": " + rs.getString("CorpName"));
                }
                conn.close();
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        System.out.println("Finished in: "+(System.currentTimeMillis()-startTime)+" milli secs");
    }

C3P0:

public static void main(String[] args) {
        long startTime=System.currentTimeMillis();
        try {
            for (int i = 0; i < 100; i++) {
                Connection conn = ConnectionManager_SQL_C3P0.getInstance().getConnection();

                String query = "SELECT * FROM MyTable;";
                PreparedStatement prest = conn.prepareStatement(query);

                ResultSet rs = prest.executeQuery();
                if (rs.next()) {
                    System.out.println(i + ": " + rs.getString("CorpName"));
                }
                conn.close();
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        System.out.println("Finished in: "+(System.currentTimeMillis()-startTime)+" milli secs");
    }

And Here is the results:

Max Pool size for c3p0 and dbcp=10
c3p0: 5534 milli secs
dbcp: 4807 milli secs
No Pooling: 2660 milli secs

__

Max Pool size for c3p0 and dbcp=100
c3p0: 4937 milli secs
dbcp: 4798 milli secs
No Pooling: 2660 milli secs

One might say the initialization and startup time of pooling libraries might affect the results of these test cases. I have repeated them with larger numbers in the loop and results are almost the same.

Surprisingly the no pooling approach is much more faster than connection pooling methods. While I assume when we close a connection physically, getting a new one must be more time consuming.

So, what's going on here?

EDIT_01: c3p0 and dbcp configurations

c3p0:

cpds.setMinPoolSize(5);
cpds.setAcquireIncrement(5);
cpds.setMaxPoolSize(100);
cpds.setMaxStatements(1000);

dbcp:

basicDataSource.setMinIdle(5);
basicDataSource.setMaxIdle(30);
basicDataSource.setMaxTotal(100);
basicDataSource.setMaxOpenPreparedStatements(180);

The rest of configurations are left as default. Worth to mention that all connections are established for a DB on localhost.

Answer 1

c3p0 is not deader than a doornail. It's old but (somewhat) actively maintained. Whether newer alternatives better suit your application is for you to decide.

What version of c3p0 are you using? If you think it is deader than a doornail, are you using an old version? You should be using 0.9.5.2.

The outcome of the test as you've defined it will be highly dependent on lots of things difficult to evaluate with the information you've provided. As Mark Rotteveel points out, you've not shown any information about your config. You've not said anything about the location of the SQL Server. You'll notice greater benefit from a Connection pool when the database is remote than when it is local, as some of the performance improvement comes from amortizing the network latency of Connection acquisition over multiple client uses. Your test executes a query and iterates through the result set. The longer the result set, the more you'll see overhead from the Connection pool (which must proxy the ResultSet) overtake the benefits of faster Connection acquisition. (The numbers you are getting look unusually bad, though. c3p0 typically has very fast ResultSet passthrough performance.) With a sufficiently long queries, the cost of Connection acquisition becomes negligible, if iterating through a ResultSet, the overhead of the pooling library increases, making a Connection pool not so useful.

But this is far from the typical use case for web or mobile clients, which usually make short queries, inserts, and updates. For short queries, inserts, and updates, the cost of a de novo Connection acquisition can be very large relative to the execution of the query. This is the use-case for which Connection pools offer a large improvement. That may not be what you are testing; it depends on how big MyTable is.