most efficient way to get a connection from a connection pool

[Linus Nikander]

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:FTPool");
}

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 address removed)

 

[David Rabinowitz]

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/patterns/ServiceLocator.html) and
then see what happens.

David

 

[Jason]

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.

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]

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

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.

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" <[email protected]> wrote in message

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:FTPool");
}

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 address removed)

 

[Sudsy]

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!

 

[me]

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