Chapter 9: Integrated Troubleshooting

 < Day Day Up > 



8.4 Application considerations for performance in database access

In this section, we discuss some best practices on database access.

  • Using connection pooling

    The Java Database Connectivity (JDBC) API provides a vendor-independent mechanism to access relational databases from Java. However, obtaining and closing a connection to a database can be a relatively expensive exercise, so the concept of connection pools has been introduced. When a database operation is to be performed, a connection can be obtained from the pool, which contains a defined number of connections to the database that have already been established. When the connection is closed, it is returned to the pool and made available for reuse. Using connection pooling can significantly reduce the overhead of obtaining a database connection. However, the connection pool is accessed via a data source. References to the data source are obtained by performing a lookup via the Java Naming and Directory Interface (JNDI). This lookup is an expensive operation, so it is good practice to perform the lookup once and cache the result for reuse. Example 8-7 on page 333 from the EJB sample shipped with DB2 UDB V8.1 shows how to use connection pooling.

    Example 8-7: Using connection pooling and PreparedStatement

    start example
     public DataSource getDataSource() throws SQLException {     private static DataSource ds = null;     if (ds == null)     {    try     {             //Create the initial naming context.          InitialContext ctx = new InitialContext();        //Perform a naming service lookup to get a DataSource object.        //The single DataSource object is a "factory" used by all        //requests to get an individual connection for each request.        ds = (DataSource)ctx.lookup("java:comp/env/AccessEmp/DataSource");     }     catch (NamingException e)     {         throw new EJBException(e);     }     return ds;     }     else     {         return ds;     } } public ArrayList getEmpNoList() {    ResultSet rs = null;    PreparedStatement ps = null;    ArrayList list = new ArrayList();    DataSource ds = null;    Connection con = null;    try    {        ds = getDataSource();        con = ds.getConnection();             String schema = getDbSchema();             StringBuffer queryBuf = new StringBuffer ("Select EMPNO, FIRSTNME from ");             queryBuf.append(schema);             queryBuf.append(".EMPLOYEE");                  ps = con.prepareStatement(queryBuf.toString());             ps.executeQuery();             rs = ps.getResultSet(); ... 
    end example

  • Releasing JDBC resources when done

    JDBC resources should always be released once they are no longer required. This includes java.sql.ResultSet, java.sql.Statement and java.sql.Connection objects, which should be closed in that order. The code to close the resources should be placed in a finally block to ensure that it is executed even when an exception condition occurs. Failure to properly close resources can cause memory leaks, and can cause slow response due to threads having to wait for a connection to become available from the pool. Since database connections in the pool are a limited resource, they should be returned to the pool once they are no longer required.

  • Using PreparedStatement

    If an application repeatedly executes the same query, but with different input parameters, then performance can be improved by using java.sql.PreparedStatement instead of java.sql.Statement. Example 8-7 on page 333 shows how to use PreparedStatement.

  • Considering SQLJ

    Use SQLJ for static complex SQL statements to avoid prepare time.

  • Using batch update

    Use batch update instead of individual UPDATE/INSERT statements to avoid unnecessary network traffic.

  • Turning off auto commit.

    Turn off autocommit() to avoid unnecessary commit flows.

  • Using stored procedure and complex SQL statement

    To avoid having to retrieve and process large amounts of data, sometimes it is beneficial to use database stored procedures for implementing some of the application logic. IBM DB2 Content Manager V8.1 is an example that pushes the application logic down to DB2 server to improve the performance and scalability. Alternatively, in some cases calls to the DB2 UDB server can be minimized by using a single statement that returns result set or multiple result sets.



 < Day Day Up > 



DB2 UDB V8 and WebSphere V5. Performance Tuning and Operations Guide2004
DB2 UDB V8 and WebSphere V5. Performance Tuning and Operations Guide2004
ISBN: N/A
EAN: N/A
Year: 2004
Pages: 90

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net