I’ve found it very difficult to find any accurate comparisons of C3P0 and DBCP. Why should I choose C3P0 over DBCP? When might DBCP be better?
Well, here’s a fairly in-depth overview of both with pros and cons and similarities and differences included.
The first thing to note is that if you’re not in a multi-threaded environment, then DBCP is going to be faster than C3P0 and will also use significantly fewer connections than C3P0. For example, using the default settings for each and sizing the pool to contain 50 connections at most, will yield results like the following single-threaded test.
Single-threaded Benchmark – 50,000 calls to getConnection()
Suffice it to say that DBCP is obviously better suited to Single-threaded applications with high load. Needing 50 connections in the pool to do the work where only 1 is needed is certainly not desired. This is the case with C3P0′s default setting of “numHelperThreads=3″. Regardless of the environment you have ( single-threaded, multi-threaded ) I would always change this setting if you forsee high load on the program. I would always use at least “numHelperThreads=5″. As a bit of background/explanation, C3P0 doesn’t actually make a connection available in the pool when it is checked-in. Instead the HelperThreads will detect these and do the work to get them back in the pool. This is great in high-load, multi-threaded environments as it avoids blocking issues. But it inherently requires a much larger number of connections to provide the same functionality ( especially if you keep “numHelperThreads=3″ ). Also, notice that DBCP is 20% faster regardless of how we tweak numHelperThreads.
Score 2 points for DBCP. 2-0.
Next what if we run the same test with a smaller connection pool size – say of only 5 connections, and numHelperThreads=3 for C3P0?
The results are a bit counterintuitive ( to me at least ). DBCP does at least manage to stay the same – very fast and reasonable ( needing just 1 connection in its pool ). But C3P0, which I expected to take longer because it previously needed all 50 connections, but actually runs faster with a smaller pool : 6.25 seconds down from 6.7 seconds. The lesson here is that C3P0 is slowing itself down with the HelperThreads having to manage all the extra connections to get them back in the pool.
Score 1 point each. DBCP for making sense and C3P0 for speeding up.
3-1, DBCP in the lead.
Now for the good stuff – the multi-threaded tests. I’ll be varying the number of Threads which will be running.
Score yet another to DBCP. Faster across the board on all accounts. So, what is it about C3P0 that has everybody ( myself included ) using it and singing its praises? Multiple threads yield multiple points again for DBCP – 2 points awarded.
5-1, C3P0 will need a miracle comeback at this point.
I know, usually Connections aren’t just checked out and run with a small SQL statement and immediately returned like the Benchmarks I’ve been running. Maybe I need to put some delay in there to more closely mimic real world performance. I’m going to throw a new setting in here which is a delay in milliseconds that each call to getConnection() will endure before finishing with the connection. I’ll give it a shot with an extra 100ms of sleep time after each SQL statement is run.
Multi-threaded – 100ms of sleep time added