hi all: According to my understanding of Threads and Connection pool, I have this theory, which I don't know if it is correct or not. Assuming a single proccessor machine, only one thread can be excuted at a time. Threads alternate, but only one thread can be excuted at one time. For simplicity, consider a web application with one servlet. Inside this servlet there is a single method that can access the database. Here is the question: 1- will it be more effecient to creat a single connection object to the connection pool, in the initilizaion proccess of the application, and leave it open, so all thread can access it sequentially? 2- or, it is more effecient to create a new connection and statement objects inside the method, and then close the connection before the method return? From my understanding of how Threads work, case one shuold eleminate unnecesarry creation of objects, every time a thread ask to access this method, and overall should be better performace. I did a test on both cases, and I was surprised that case number 2 won. The time needed to create about 5000 connection in 5 threads was much less than in case number 1. any help in uderstanding what is going on or ideas are appreciated
SCJD 1.4<br />SCJP 1.4<br />-----------------------------------<br />"With regard to excellence, it is not enough to know, but we must try to have and use it.<br />" Aristotle
Well, in a Connection Pool you would want more than one Connection object, otherwise it is not a Pool of connections. There is a balance between the number of Connections you have in the pool and the number of concurrent users you expect to use at the same time. If you have only one connection in the pool then all users will have to wait their turn to access the connection. Whereas, in your second example, they only have to wait for the instantiation of you individual class for that client. Does that make any sense? Mark
hi Mark Spritzler: Thanks for your replay. I understand the advantages of the connection pool. What I don't understand is that: If only one thread can be executed at one time. Consider 2 client threads accessing the same methods. They can't be executed in paralle. They have to take turns. Then, having two connection objects to the poole will not do any good, they have to wait anyway. What do you think?
Yes, the threads do have to "take turns", but they don't get to decide when to let another thread take their turn. So, if they share the same Connection object, and thus the same transaction, one thread can totally screw up another thread's transaction (by rolling it back, adding updates to it, etc.). Does that make sense?
James Carman, President<br />Carman Consulting, Inc.
Note that on the database side there will be a separate Thread for each connection. Executing a query does not require continuous CPU effort - there are periods (sometimes extensive) when a Thread is waiting for disk IO or network IO. Therefore multiple connections can make better use of the database CPU. Bill