most efficient way to get a connection from a connection pool

Discussion in 'Java' started by Linus Nikander, Sep 15, 2003.

  1. 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 -
    Linus Nikander, Sep 15, 2003
    #1
    1. Advertising

  2. 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

    "Linus Nikander" <> wrote in message
    news:_ge9b.102346$...
    > 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 -
    >
    >
    David Rabinowitz, Sep 15, 2003
    #2
    1. Advertising

  3. Linus Nikander

    Jason Guest

    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" <> wrote in message news:<_ge9b.102346$>...
    > 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 -
    Jason, Sep 15, 2003
    #3
  4. 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" <> wrote in message
    news:...
    > 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" <> wrote in message

    news:<_ge9b.102346$>...
    > > 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 -
    Linus Nikander, Sep 15, 2003
    #4
  5. Linus Nikander

    Sudsy Guest

    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!
    Sudsy, Sep 16, 2003
    #5
  6. Linus Nikander

    me Guest

    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" <> wrote in message
    news:_ge9b.102346$...
    > 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 -
    >
    >
    me, Sep 18, 2003
    #6
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Brent Minder
    Replies:
    3
    Views:
    392
    Brent
    Dec 28, 2003
  2. Peter
    Replies:
    1
    Views:
    364
    Steve C. Orr [MVP, MCSD]
    Nov 9, 2004
  3. Razvan
    Replies:
    11
    Views:
    528
    Dale King
    Oct 12, 2004
  4. defn noob
    Replies:
    2
    Views:
    286
    defn noob
    Jul 2, 2008
  5. Replies:
    9
    Views:
    143
    smallpond
    Jan 4, 2010
Loading...

Share This Page