3

I have been through these questions - choose a db connection pool , Is DB connection pooling all that important and java - DataSource for standalone application - no application server

and those don't answer my curiosity.

What I have is a standalone multi threaded Java application which is supposed to perform a data load to DB in an off or low load window but has to be fast enough to complete within a limited time.

Number of Java threads are configurable but limited to a maximum number.

As far as DB connections go, I am currently getting a new connection for each thread and closing it down when that thread is done. My reasons of not using third party DB connection pool are,

1.Number of maximum Java threads are limited to fixed limit and that limit is manageable by DB ( Its DB2 database )

2.Avoid unnecessary wait for connection from DB pools and avoid clash or wait times among multiple threads ( in case no connections in pool are free )

so in my scenario, would DB connection pool really be needed or would I face any challenges in long run or would that just be a nice to have feature?

Connection Pools make sense in case of web apps since you don't know number of requests/threads in advance but I am not sure about any advantage for standalone apps with fixed max threads.

I am thinking of using C3P0 connection pools if needed.

Community
  • 1
  • 1
Sabir Khan
  • 9,826
  • 7
  • 45
  • 98
  • Potential duplicate; http://stackoverflow.com/questions/27318792/would-a-connection-pool-benefit-a-multithreaded-java-program?rq=1 – Mark Rotteveel Mar 21 '16 at 09:21

2 Answers2

2

Using a connection pool has a few advantages over not using one;

  1. At thread start time, if a connection is available in the pool then the thread will not block to open a new connection on start.
  2. A connection can be returned to the pool when one of your threads completes, this reduces the load on the database server as client connections persist (through the application execution); instead of being set up and torn down multiple times

Simply limit the max number of connections in your pool to match your mentioned database limits and make sure you return your connections the pool (with c3p0 I believe you just close the Connection, which is wrapped by their PooledConnection).

Elliott Frisch
  • 198,278
  • 20
  • 158
  • 249
1

Generally, a connection pool reduces the time when establishing a connection to the database, because it reuses connections, i.e., instead of opening and closing connections, it opens several connections and manages these connections (e.g., closes the connection after a specific amount of time, always have a pool of connections available, ...).

That said, I'm not sure that your application would benefit from a connection pool (maybe you want to benchmark it). If you have a standalone application with a fixed number of threads, the performance of the application might benefit from a connection pool, e.g., if connections can be re-used. That means, if your application starts all threads at the beginning and finalizes each computation and closes the connection, a connection pool may not help. But if the threads are randomly started and closed that might be of benefit.

Regarding the connection pool implementation to use, personally I used C3P0 and HikariCP (https://brettwooldridge.github.io/HikariCP/). I can highly recommend the latter - but that's just a personal opinion.

Philipp
  • 1,289
  • 1
  • 16
  • 37
  • Thanks for pointing out connection open and close times. All application threads are started in beginning and once we finalize an optimal number of threads for target machine, number of threads would more or less become fixed too. – Sabir Khan Mar 21 '16 at 05:45