Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Java > most efficient way to get a connection from a connection pool

Reply
Thread Tools

most efficient way to get a connection from a connection pool

 
 
Linus Nikander
Guest
Posts: n/a
 
      09-15-2003
Having recently load-tested the application we are developing I noticed that
one of the most expensive (time-wise) calls was my fetch of a db-connection
from the defined db-pool. At present I fetch my connections using :


private Connection getConnection() throws SQLException {
try {
Context jndiCntx = new InitialContext();
DataSource ds =
(DataSource)
jndiCntx.lookup("java:comp/env/jdbc/txDatasource");
return ds.getConnection();
} catch (NamingException ne) {
myLog.error(this.makeSQLInsertable("getConnection - could not
find connection"));
throw new EJBException(ne);
}
}


In other parts of the code, not developed by the same team, I've seen the
same task accomplished by :

private Connection getConnection() throws SQLException {
return DriverManager.getConnection("jdbc:weblogic:jts:FTP ool");
}

From the performance-measurements I made the latter seems to be much more
efficient (time-wise). To give you some metrics:

The first version took a total of 75724ms for a total of 7224 calls which
gives ~ 11ms/call
The second version took a total of 8127ms for 11662 calls which gives
~0,7ms/call

I'm no JDBC guru som i'm probably missing something vital here. One
suspicion I have is that the second call first find the jdbc-pool and after
that makes the very same (DataSource)
jndiCntx.lookup("java:comp/env/jdbc/txDatasource") in order to fetch the
actual connection anyway. If that is true then my comparison is plain wrong
since one call is part of the second. If not, then the second version sure
seems a lot faster.

Apart from the obvious performance-differences in the two above approaches,
is there any other difference one should be aware of (transaction-context
for instance) between the two ? Basically I'm working in an EJB-environment
on weblogic 7.0 and looking for the most efficient way to get hold of a
db-connection in code. Comments anyone ?

//Linus Nikander - http://www.velocityreviews.com/forums/(E-Mail Removed)


 
Reply With Quote
 
 
 
 
David Rabinowitz
Guest
Posts: n/a
 
      09-15-2003
The question is whether the delay is in the DataSource itself, or in the
JNDI call. Try keeping the DataSource reference (see the ServiceLocator
pattern http://java.sun.com/blueprints/patte...ceLocator.html) and
then see what happens.

David

"Linus Nikander" <(E-Mail Removed)> wrote in message
news:_ge9b.102346$(E-Mail Removed)...
> Having recently load-tested the application we are developing I noticed

that
> one of the most expensive (time-wise) calls was my fetch of a

db-connection
> from the defined db-pool. At present I fetch my connections using :
>
>
> private Connection getConnection() throws SQLException {
> try {
> Context jndiCntx = new InitialContext();
> DataSource ds =
> (DataSource)
> jndiCntx.lookup("java:comp/env/jdbc/txDatasource");
> return ds.getConnection();
> } catch (NamingException ne) {
> myLog.error(this.makeSQLInsertable("getConnection - could not
> find connection"));
> throw new EJBException(ne);
> }
> }
>
>
> In other parts of the code, not developed by the same team, I've seen the
> same task accomplished by :
>
> private Connection getConnection() throws SQLException {
> return DriverManager.getConnection("jdbc:weblogic:jts:FTP ool");
> }
>
> From the performance-measurements I made the latter seems to be much more
> efficient (time-wise). To give you some metrics:
>
> The first version took a total of 75724ms for a total of 7224 calls which
> gives ~ 11ms/call
> The second version took a total of 8127ms for 11662 calls which gives
> ~0,7ms/call
>
> I'm no JDBC guru som i'm probably missing something vital here. One
> suspicion I have is that the second call first find the jdbc-pool and

after
> that makes the very same (DataSource)
> jndiCntx.lookup("java:comp/env/jdbc/txDatasource") in order to fetch the
> actual connection anyway. If that is true then my comparison is plain

wrong
> since one call is part of the second. If not, then the second version sure
> seems a lot faster.
>
> Apart from the obvious performance-differences in the two above

approaches,
> is there any other difference one should be aware of (transaction-context
> for instance) between the two ? Basically I'm working in an

EJB-environment
> on weblogic 7.0 and looking for the most efficient way to get hold of a
> db-connection in code. Comments anyone ?
>
> //Linus Nikander - (E-Mail Removed)
>
>



 
Reply With Quote
 
 
 
 
Jason
Guest
Posts: n/a
 
      09-15-2003
You're initializing everything each time. That equates directly to
time. Now, I'm kind of assuming that you're doing this in a web
implementation, so your milage may vary.

Create a Singleton object that contains the DataSource object (only
one for the whole app and shared across users). Initialize it either
when the app starts up or say when the first user logs into the app
after a restart. Then your calls to get the connection will look more
like the second sample you provided.

Code:
public void init(ServletConfig config) throws ServletException {
super.init(config) ;

DatabaseResources dbResources = DatabaseResources.getInstance() ;

Context context = null ;

try{
Hashtable environment = new Hashtable() ;
environment.put(Context.INITIAL_CONTEXT_FACTORY,
"com.ibm.websphere.naming.WsnInitialContextFactory") ;

context = new InitialContext(environment) ;

DataSource dbSource = (DataSource) context.lookup(
getServletContext().getInitParameter("dataSourceName")) ;

dbResources.setDataSource(dbSource) ;
context.close() ;
}
catch(Exception theException){
System.err.println(theException.toString() + " : Generated in "
+ getClass().getName() + ".init() method") ;
theException.printStackTrace() ;
}
}


Now, here's the code for the DatabaseResources singleton object

/**
* Gets the instance
* @return Returns a DatabaseResources
*/
public static DatabaseResources getInstance() {
if( instance == null )
instance = new DatabaseResources() ;
return instance;
}


Now, here's the code to get the connection.  This exists in a
superclass for all of my database access objects so this code only
exists once in the app.

/**
* Initialize the database connection with the provided user id &
password
* @param String userID
* @param String password
* @throws SQLException
*/
public void init(String userID, String password) throws SQLException
{
DatabaseResources dbResources = DatabaseResources.getInstance() ;
DataSource dataSource = dbResources.getDataSource() ;
connection = dataSource.getConnection( userID, password ) ;
}
Since all of my Classes that have to do database access ultimately
subclass the class where init(String, String) is located, it's always
available and makes getting the connection a single line call. One
important note. init() is called inside a try{} and in the finally{}
before we leave the method and after we've done the data operations I
make the necessary calls to close the connections and such. This is
very important if you don't want hanging connections which make your
application server very unhappy.
"Linus Nikander" <(E-Mail Removed)> wrote in message news:<_ge9b.102346$(E-Mail Removed)>...
> Having recently load-tested the application we are developing I noticed that
> one of the most expensive (time-wise) calls was my fetch of a db-connection
> from the defined db-pool. At present I fetch my connections using :
>
>
> private Connection getConnection() throws SQLException {
> try {
> Context jndiCntx = new InitialContext();
> DataSource ds =
> (DataSource)
> jndiCntx.lookup("java:comp/env/jdbc/txDatasource");
> return ds.getConnection();
> } catch (NamingException ne) {
> myLog.error(this.makeSQLInsertable("getConnection - could not
> find connection"));
> throw new EJBException(ne);
> }
> }
>
>
> In other parts of the code, not developed by the same team, I've seen the
> same task accomplished by :
>
> private Connection getConnection() throws SQLException {
> return DriverManager.getConnection("jdbc:weblogic:jts:FTP ool");
> }
>
> From the performance-measurements I made the latter seems to be much more
> efficient (time-wise). To give you some metrics:
>
> The first version took a total of 75724ms for a total of 7224 calls which
> gives ~ 11ms/call
> The second version took a total of 8127ms for 11662 calls which gives
> ~0,7ms/call
>
> I'm no JDBC guru som i'm probably missing something vital here. One
> suspicion I have is that the second call first find the jdbc-pool and after
> that makes the very same (DataSource)
> jndiCntx.lookup("java:comp/env/jdbc/txDatasource") in order to fetch the
> actual connection anyway. If that is true then my comparison is plain wrong
> since one call is part of the second. If not, then the second version sure
> seems a lot faster.
>
> Apart from the obvious performance-differences in the two above approaches,
> is there any other difference one should be aware of (transaction-context
> for instance) between the two ? Basically I'm working in an EJB-environment
> on weblogic 7.0 and looking for the most efficient way to get hold of a
> db-connection in code. Comments anyone ?
>
> //Linus Nikander - (E-Mail Removed)

 
Reply With Quote
 
Linus Nikander
Guest
Posts: n/a
 
      09-15-2003
Thank you for your reply. What you say makes perfect sense. And as somebody
else already pointed out what I'm looking for is to implement the service
locator pattern (This will also benifit all our EBJs since they also perform
JNDI-lookups every single time). Thanks to you I don't even have to code it
! it'll be interesting to see how big a performance gain this will turn out
to be.
//linus


"Jason" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed) om...
> You're initializing everything each time. That equates directly to
> time. Now, I'm kind of assuming that you're doing this in a web
> implementation, so your milage may vary.
>
> Create a Singleton object that contains the DataSource object (only
> one for the whole app and shared across users). Initialize it either
> when the app starts up or say when the first user logs into the app
> after a restart. Then your calls to get the connection will look more
> like the second sample you provided.
>
>
Code:
> public void init(ServletConfig config) throws ServletException {
> super.init(config) ;
>
> DatabaseResources dbResources = DatabaseResources.getInstance() ;
>
> Context context = null ;
>
> try{
> Hashtable environment = new Hashtable() ;
> environment.put(Context.INITIAL_CONTEXT_FACTORY,
> "com.ibm.websphere.naming.WsnInitialContextFactory") ;
>
> context = new InitialContext(environment) ;
>
> DataSource dbSource = (DataSource) context.lookup(
> getServletContext().getInitParameter("dataSourceName")) ;
>
> dbResources.setDataSource(dbSource) ;
> context.close() ;
> }
> catch(Exception theException){
> System.err.println(theException.toString() + " : Generated in "
> + getClass().getName() + ".init() method") ;
> theException.printStackTrace() ;
> }
> }
>
>
> Now, here's the code for the DatabaseResources singleton object
>
> /**
> * Gets the instance
> * @return Returns a DatabaseResources
> */
> public static DatabaseResources getInstance() {
> if( instance == null )
> instance = new DatabaseResources() ;
> return instance;
> }
>
>
> Now, here's the code to get the connection.  This exists in a
> superclass for all of my database access objects so this code only
> exists once in the app.
>
> /**
> * Initialize the database connection with the provided user id &
> password
> * @param String userID
> * @param String password
> * @throws SQLException
> */
> public void init(String userID, String password) throws SQLException
> {
> DatabaseResources dbResources = DatabaseResources.getInstance() ;
> DataSource dataSource = dbResources.getDataSource() ;
> connection = dataSource.getConnection( userID, password ) ;
> }
>
>
>
>
> Since all of my Classes that have to do database access ultimately
> subclass the class where init(String, String) is located, it's always
> available and makes getting the connection a single line call. One
> important note. init() is called inside a try{} and in the finally{}
> before we leave the method and after we've done the data operations I
> make the necessary calls to close the connections and such. This is
> very important if you don't want hanging connections which make your
> application server very unhappy.
> "Linus Nikander" <(E-Mail Removed)> wrote in message

news:<_ge9b.102346$(E-Mail Removed)>...
> > Having recently load-tested the application we are developing I noticed

that
> > one of the most expensive (time-wise) calls was my fetch of a

db-connection
> > from the defined db-pool. At present I fetch my connections using :
> >
> >
> > private Connection getConnection() throws SQLException {
> > try {
> > Context jndiCntx = new InitialContext();
> > DataSource ds =
> > (DataSource)
> > jndiCntx.lookup("java:comp/env/jdbc/txDatasource");
> > return ds.getConnection();
> > } catch (NamingException ne) {
> > myLog.error(this.makeSQLInsertable("getConnection - could

not
> > find connection"));
> > throw new EJBException(ne);
> > }
> > }
> >
> >
> > In other parts of the code, not developed by the same team, I've seen

the
> > same task accomplished by :
> >
> > private Connection getConnection() throws SQLException {
> > return DriverManager.getConnection("jdbc:weblogic:jts:FTP ool");
> > }
> >
> > From the performance-measurements I made the latter seems to be much

more
> > efficient (time-wise). To give you some metrics:
> >
> > The first version took a total of 75724ms for a total of 7224 calls

which
> > gives ~ 11ms/call
> > The second version took a total of 8127ms for 11662 calls which gives
> > ~0,7ms/call
> >
> > I'm no JDBC guru som i'm probably missing something vital here. One
> > suspicion I have is that the second call first find the jdbc-pool and

after
> > that makes the very same (DataSource)
> > jndiCntx.lookup("java:comp/env/jdbc/txDatasource") in order to fetch the
> > actual connection anyway. If that is true then my comparison is plain

wrong
> > since one call is part of the second. If not, then the second version

sure
> > seems a lot faster.
> >
> > Apart from the obvious performance-differences in the two above

approaches,
> > is there any other difference one should be aware of

(transaction-context
> > for instance) between the two ? Basically I'm working in an

EJB-environment
> > on weblogic 7.0 and looking for the most efficient way to get hold of a
> > db-connection in code. Comments anyone ?
> >
> > //Linus Nikander - (E-Mail Removed)



 
Reply With Quote
 
Sudsy
Guest
Posts: n/a
 
      09-16-2003
Linus Nikander wrote:
> Having recently load-tested the application we are developing I noticed that
> one of the most expensive (time-wise) calls was my fetch of a db-connection
> from the defined db-pool. At present I fetch my connections using :
>
>
> private Connection getConnection() throws SQLException {
> try {
> Context jndiCntx = new InitialContext();
> DataSource ds =
> (DataSource)
> jndiCntx.lookup("java:comp/env/jdbc/txDatasource");
> return ds.getConnection();
> } catch (NamingException ne) {
> myLog.error(this.makeSQLInsertable("getConnection - could not
> find connection"));
> throw new EJBException(ne);
> }
> }


The expensive part here is the JNDI lookup. The DataSource
reference should be very long-lived; you should only need
to perform the lookup if the getConnection fails. That
single change will significantly improve your performance!

 
Reply With Quote
 
me
Guest
Posts: n/a
 
      09-18-2003
You dont need to be getting the InitalContext and the DataSource each time.
Wrap in something like a getConnection method, stick that inside of an
AbstractDAO
and have each DAO extend the AbstractDAO, then to get the connection, just
do:
Connection con = getConnection();
By the same token, you can have a cleanup method that releases stuff also
and put that into
the AbstractDAO. This makes for a much cleaner implementation, and also
makes the database
stuff easier to do, e.g.,., use toad to develop sql, stick sql into the dao,
make value object(s)
from the resultset, and ship it/them back to the caller.

public class AbstractDAO {
static DataSource ds = null;
static Context ctx = null;
public getConnection() {
if (ctx == null) ctx = new InitialContext();
if (ds == null) ds = ctx.lookup("java:comp/env/jdbc/txDatasource");
return ds.getConnection();
}
}

or you can use a static initializer, either will work.
Hope this helps...
--S

"Linus Nikander" <(E-Mail Removed)> wrote in message
news:_ge9b.102346$(E-Mail Removed)...
> Having recently load-tested the application we are developing I noticed

that
> one of the most expensive (time-wise) calls was my fetch of a

db-connection
> from the defined db-pool. At present I fetch my connections using :
>
>
> private Connection getConnection() throws SQLException {
> try {
> Context jndiCntx = new InitialContext();
> DataSource ds =
> (DataSource)
> jndiCntx.lookup("java:comp/env/jdbc/txDatasource");
> return ds.getConnection();
> } catch (NamingException ne) {
> myLog.error(this.makeSQLInsertable("getConnection - could not
> find connection"));
> throw new EJBException(ne);
> }
> }
>
>
> In other parts of the code, not developed by the same team, I've seen the
> same task accomplished by :
>
> private Connection getConnection() throws SQLException {
> return DriverManager.getConnection("jdbc:weblogic:jts:FTP ool");
> }
>
> From the performance-measurements I made the latter seems to be much more
> efficient (time-wise). To give you some metrics:
>
> The first version took a total of 75724ms for a total of 7224 calls which
> gives ~ 11ms/call
> The second version took a total of 8127ms for 11662 calls which gives
> ~0,7ms/call
>
> I'm no JDBC guru som i'm probably missing something vital here. One
> suspicion I have is that the second call first find the jdbc-pool and

after
> that makes the very same (DataSource)
> jndiCntx.lookup("java:comp/env/jdbc/txDatasource") in order to fetch the
> actual connection anyway. If that is true then my comparison is plain

wrong
> since one call is part of the second. If not, then the second version sure
> seems a lot faster.
>
> Apart from the obvious performance-differences in the two above

approaches,
> is there any other difference one should be aware of (transaction-context
> for instance) between the two ? Basically I'm working in an

EJB-environment
> on weblogic 7.0 and looking for the most efficient way to get hold of a
> db-connection in code. Comments anyone ?
>
> //Linus Nikander - (E-Mail Removed)
>
>



 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
most efficient way to get number of files in a directory guba@vi-anec.de Perl Misc 9 01-04-2010 06:03 PM
Most efficient way to get pixelcolors of an image? defn noob Python 2 07-02-2008 08:25 PM
Most Efficient Way of Exporting CSV data from page Peter ASP .Net 1 11-09-2004 10:41 PM
Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached. Guoqi Zheng ASP .Net 4 06-03-2004 06:39 PM
What is the most efficient way to access common fcts on asp.net pages when using user controls? Brent Minder ASP .Net 3 12-28-2003 02:28 PM



Advertisments