but I am not sure pool will optimize "connect" if it executes validation queries and runs background threads
> I connection pool is done right, it only has one background thread for performing tasks that cannot be done synchronously with client requests. Chencking connections can be done in client thread just before connection is returned to the client. In order to prevent performance problems of frequent connection checks, you can have checkTime paramter which tells the pool not to check connections if less then checkTime is elapsed since last time connection is successfully returned to the pool. Yes, this way client still can get bad connection but probability of that is small and can be tuned.
> >> So I think it is better without pool than pool with workarounds.
> If you have an application that have 'long running queries' (say 5-15 sec for some analytic query) or more then 10-20 simultaneous users the pool is a must.
I have used pools for some projects a year ago, DBCP is one of them, but
I use this way for last two web applications :
1. Popular MVC design pattern.
2. Lazy ThreadLocal connections and transactions.
3. No Connection pool.
4. Pluggable JDBC decorators for logging, monitoring and performance tuning.
ThreadLocal connections work this way:
Static method opens *new* connection and sets ThreadLocal field on the first
"getConnection()" for thread/request.
Controler *closes* connection on the end of request if ThreadLocal connection is open (I use java.lang.ThreadLocal to implement it).
I can not forget to close connection (there is single place in app to close it), Requests never block (I do not need to wait connections), I never have too many open connections (Thread pool/maxProcesors on web server limits it),I do not have broken connections, no thread, no validation and no workarounds. Possible I will need a pool in the future, but I do not think so after I have tested the most simple way (connections and authentication are very fast on our servers). The cache helps too, ~90% of requests do not need to open connection.
BTW It must be possible to optimize 5-15 sec analytic queries, there are a few ways to do it:
1. Generate static content on data import.
2. Cache query results in memory.
3. Add more indexes and optimize query itself.
4. Lucene for search queries.
I think you know a lot of good ways yourself and never execute this kind of queries for each request, It is much better optimization than connection pool.
Pool is a good optimization if your connection is very "slow", but I do not think you use slow internet connection for JDBC, XML RPC is more popular for this use case, is not it ?
Test boths ways yourself, I am not sure it will work for you, but it works for me and it works better.
> If pool is written well (there is no time consuming tasks (connecting, checking and closing connections) in synchronous blocks, there is only one background thread for housekeeping, there is configurable connection checking (in my opinion the best time for connection checking is just before connection is returned to the client)), there is no workarounds, it is just ensuring that client gets valid connection. If you have only one connection, you still have to ensure that this connection stays valid all the time.