most efficient way to get a connection from a connection pool

**David Rabinowitz** · Jul 17 '05, 09:49 PM

Re: most efficient way to get a connection from a connection pool

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" <linus@nikander .net> wrote in message
news:_ge9b.1023 46$zL.438@news1 .bredband.com.. .[color=blue]
> Having recently load-tested the application we are developing I noticed[/color]
that[color=blue]
> one of the most expensive (time-wise) calls was my fetch of a[/color]
db-connection[color=blue]
> 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.getConnectio n();
> } catch (NamingExceptio n ne) {
> myLog.error(thi s.makeSQLInsert able("getConnec tion - 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.g etConnection("j dbc:weblogic:jt s: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[/color]
after[color=blue]
> 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[/color]
wrong[color=blue]
> 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[/color]
approaches,[color=blue]
> is there any other difference one should be aware of (transaction-context
> for instance) between the two ? Basically I'm working in an[/color]
EJB-environment[color=blue]
> on weblogic 7.0 and looking for the most efficient way to get hold of a
> db-connection in code. Comments anyone ?
>
> //Linus Nikander - linus@nikander. net
>
>[/color]

**Jason** · Jul 17 '05, 09:49 PM

Re: most efficient way to get a connection from a connection pool

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" <linus@nikander .net> wrote in message news:<_ge9b.102 346$zL.438@news 1.bredband.com> ...[color=blue]
> 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.getConnectio n();
> } catch (NamingExceptio n ne) {
> myLog.error(thi s.makeSQLInsert able("getConnec tion - 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.g etConnection("j dbc:weblogic:jt s: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 - linus@nikander. net[/color]

**Linus Nikander** · Jul 17 '05, 09:49 PM

Re: most efficient way to get a connection from a connection pool

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" <jptryon@tva.go v> wrote in message
news:471c5353.0 309151219.47010 be0@posting.goo gle.com...[color=blue]
> 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" <linus@nikander .net> wrote in message[/color]
news:<_ge9b.102 346$zL.438@news 1.bredband.com> ...[color=blue][color=green]
> > Having recently load-tested the application we are developing I noticed[/color][/color]
that[color=blue][color=green]
> > one of the most expensive (time-wise) calls was my fetch of a[/color][/color]
db-connection[color=blue][color=green]
> > 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.getConnectio n();
> > } catch (NamingExceptio n ne) {
> > myLog.error(thi s.makeSQLInsert able("getConnec tion - could[/color][/color]
not[color=blue][color=green]
> > find connection"));
> > throw new EJBException(ne );
> > }
> > }
> >
> >
> > In other parts of the code, not developed by the same team, I've seen[/color][/color]
the[color=blue][color=green]
> > same task accomplished by :
> >
> > private Connection getConnection() throws SQLException {
> > return DriverManager.g etConnection("j dbc:weblogic:jt s:FTPool");
> > }
> >
> > From the performance-measurements I made the latter seems to be much[/color][/color]
more[color=blue][color=green]
> > efficient (time-wise). To give you some metrics:
> >
> > The first version took a total of 75724ms for a total of 7224 calls[/color][/color]
which[color=blue][color=green]
> > 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[/color][/color]
after[color=blue][color=green]
> > 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[/color][/color]
wrong[color=blue][color=green]
> > since one call is part of the second. If not, then the second version[/color][/color]
sure[color=blue][color=green]
> > seems a lot faster.
> >
> > Apart from the obvious performance-differences in the two above[/color][/color]
approaches,[color=blue][color=green]
> > is there any other difference one should be aware of[/color][/color]
(transaction-context[color=blue][color=green]
> > for instance) between the two ? Basically I'm working in an[/color][/color]
EJB-environment[color=blue][color=green]
> > on weblogic 7.0 and looking for the most efficient way to get hold of a
> > db-connection in code. Comments anyone ?
> >
> > //Linus Nikander - linus@nikander. net[/color][/color]

me · Jul 17 '05, 09:50 PM

Re: most efficient way to get a connection from a connection pool

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("jav a:comp/env/jdbc/txDatasource");
return ds.getConnectio n();
}
}

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

"Linus Nikander" <linus@nikander .net> wrote in message
news:_ge9b.1023 46$zL.438@news1 .bredband.com.. .[color=blue]
> Having recently load-tested the application we are developing I noticed[/color]
that[color=blue]
> one of the most expensive (time-wise) calls was my fetch of a[/color]
db-connection[color=blue]
> 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.getConnectio n();
> } catch (NamingExceptio n ne) {
> myLog.error(thi s.makeSQLInsert able("getConnec tion - 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.g etConnection("j dbc:weblogic:jt s: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[/color]
after[color=blue]
> 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[/color]
wrong[color=blue]
> 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[/color]
approaches,[color=blue]
> is there any other difference one should be aware of (transaction-context
> for instance) between the two ? Basically I'm working in an[/color]
EJB-environment[color=blue]
> on weblogic 7.0 and looking for the most efficient way to get hold of a
> db-connection in code. Comments anyone ?
>
> //Linus Nikander - linus@nikander. net
>
>[/color]