File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes Servlets and the fly likes Servlet, Threads, and connection pool Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of Java Interview Guide this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Java » Servlets
Bookmark "Servlet, Threads, and connection pool" Watch "Servlet, Threads, and connection pool" New topic

Servlet, Threads, and connection pool

Hanna Habashy
Ranch Hand

Joined: Aug 20, 2003
Posts: 532
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
Mark Spritzler

Joined: Feb 05, 2001
Posts: 17276

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?

Perfect World Programming, LLC - iOS Apps
How to Ask Questions the Smart Way FAQ
Hanna Habashy
Ranch Hand

Joined: Aug 20, 2003
Posts: 532
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?
James Carman
Ranch Hand

Joined: Feb 20, 2001
Posts: 580
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.
William Brogden
Author and all-around good cowpoke

Joined: Mar 22, 2000
Posts: 13037
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.
I agree. Here's the link:
subject: Servlet, Threads, and connection pool
It's not a secret anymore!