JDBC Resources and Connection Pool : Explain

JDBC Resources

A JDBC resource (data source) provides applications with a means of connecting to a database. Typically, the administrator creates a JDBC resource for each database accessed by the applications deployed in a domain. (However, more than one JDBC resource can be created for a database.)

 

JDBC Resources

To store, organize, and retrieve data, most applications use relational databases. J2EE applications access relational databases through the JDBC API.

To create a JDBC resource, specify a unique JNDI name that identifies the resource. (See the section JNDI Names and Resources.) Expect to find the JNDI name of a JDBC resource in java:comp/env/jdbc subcontext. For example, the JNDI name for the resource of a payroll database could be java:comp/env/jdbc/payrolldb. Because all resource JNDI names are in the java:comp/env subcontext, when specifying the JNDI name of a JDBC resource in the Admin Console, enter only jdbc/name. For example, for a payroll database specify jdbc/payrolldb.

JDBC Connection Pools

To create a JDBC resource, specify the connection pool with which it is associated. Multiple JDBC resources can specify a single connection pool.

A JDBC connection pool is a group of reusable connections for a particular database. Because creating each new physical connection is time consuming, the server maintains a pool of available connections to increase performance. When an application requests a connection, it obtains one from the pool. When an application closes a connection, the connection is returned to the pool.

The properties of connection pools can vary with different database vendors. Some common properties are the database’s name (URL), user name, and password.

How JDBC Resources and Connection Pools Work Together

To store, organize, and retrieve data, most applications use relational databases. Java EE applications access relational databases through the JDBC API. Before an application can access a database, it must get a connection.

At runtime, here’s what happens when an application connects to a database:

  1. The application gets the JDBC resource (data source) associated with the database by making a call through the JNDI API.

    Given the resource’s JNDI name, the naming and directory service locates the JDBC resource. Each JDBC resource specifies a connection pool.

  2. Via the JDBC resource, the application gets a database connection.

    Behind the scenes, the application server retrieves a physical connection from the connection pool that corresponds to the database. The pool defines connection attributes such as the database name (URL), user name, and password.

  3. Now that it’s connected to the database, the application can read, modify, and add data to the database.

    The applications access the database by making calls to the JDBC API. The JDBC driver translates the application’s JDBC calls into the protocol of the database server.

  4. When it’s finished accessing the database, the application closes the connection.

    The application server returns the connection to the connection pool. Once it’s back in the pool, the connection is available for the next application.

    Sun Java System JDBC Driver for Oracle 8.1.7 and 9.x Databases

    The JAR files for this driver are smbase.jar, smoracle.jar, and smutil.jar. Configure the connection pool using the following settings:

    • Name: Use this name when you configure the JDBC resource later.

    • Resource Type: Specify the appropriate value.

    • Database Vendor: Oracle

    • DataSource Classname: com.sun.sql.jdbcx.oracle.OracleDataSource

    • Properties:

      • serverName – Specify the host name or IP address of the database server.

      • portNumber – Specify the port number of the database server.

      • SID – Set as appropriate.

      • user – Set as appropriate.

      • password – Set as appropriate.

    • URL: jdbc:sun:oracle://serverName[:portNumber][;SID=databaseName]


    Sun Java System JDBC Driver for Microsoft SQL Server Databases

    The JAR files for this driver are smbase.jar, smsqlserver.jar, and smutil.jar. Configure the connection pool using the following settings:

    • Name: Use this name when you configure the JDBC resource later.

    • Resource Type: Specify the appropriate value.

    • Database Vendor: mssql

    • DataSource Classname: com.sun.sql.jdbcx.sqlserver.SQLServerDataSource

    • Properties:

      • serverName – Specify the host name or IP address and the port of the database server.

      • portNumber – Specify the port number of the database server.

      • user – Set as appropriate.

      • password – Set as appropriate.

      • selectMethod – Set to cursor.

    • URL: jdbc:sun:sqlserver://serverName[:portNumber]

Explain JIT

Just-In-Time Compilers

The simplest tool used to increase the performance of your application is the Just-In-Time (JIT) compiler. A JIT is a code generator that converts Java bytecode into native machine code. Java programs invoked with a JIT generally run much faster than when the bytecode is executed by the interpreter. The Java Hotspot VM removes the need for a JIT compiler in most cases however you may still find the JIT compiler being used in earlier releases.

The JIT compiler was first made available as a performance update in the Java Development Kit (JDK) 1.1.6 software release and is now a standard tool invoked whenever you use the java interpreter command in the Java 2 platform release. You can disable the JIT compiler using the -Djava.compiler=NONE option to the Java VM. This is covered in more detail at the end of the JIT section.

How do JIT Compilers work?

JIT compilers are supplied as standalone platform-dependent native libraries. If the JIT Compiler library exists, the Java VM initializes Java Native Interface (JNI) native code hooks to call JIT functions available in that library instead of the equivalent function in the interpreter.

The java.lang.Compiler class is used to load the native library and start the initialization inside the JIT compiler.

When the Java VM invokes a Java method, it uses an invoker method as specified in the method block of the loaded class object. The Java VM has several invoker methods, for example, a different invoker is used if the method is synchronized or if it is a native method.

The JIT compiler uses its own invoker. Sun production releases check the method access bit for value ACC_MACHINE_COMPILED to notify the interpreter that the code for this method has already been compiled and stored in the loaded class.

When does the code become JIT compiled code?

When a method is called the first time the JIT compiler compiles the method block into native code for this method and stored that in the code block for that method.

Once the code has been compiled the ACC_MACHINE_COMPILED bit, which is used on the Sun platform, is set.

How Synchronization impacts performance

  • Avoid synchronization where possible.
  • ArrayList is faster than Vector
  • Only List and Map have efficient thread-safe implementations: the Vector and Hashtable classes respectively.
  • Synchronized methods are slower than the identical non-synchronized one.
  • Consider using non-synchronized classes and synchronized-wrappers.
  • FastVector is faster than Vector by making the elementData field public, thus avoiding (synchronized) calls to elementAt().
  • Use threads. Prioritize threads. Use notify instead of notifyAll. Use synchronization sparingly.
  • use methods in order: static > final > instance > interface > synchronized
  • Because synchronization synchronizes thread memory with main memory, there is a cost to synchronization beyond simply acquiring a lock.
  • Make synchronized blocks as short as possible.
  • Spread synchronizations over more than one lock.
  • Minimizing synchronization may take work, but can pay off well.
  • Minimize the synchronized block in the service method.

How to improve Servlet performance

  • Use the servlet init() method to cache static data, and release them in the destroy() method.
  • Use StringBuffer rather than using + operator when you concatenate multiple strings.
  • Use the print() method rather than the println() method.
  • Use a ServletOutputStream rather than a PrintWriter to send binary data.
  • Initialize the PrintWriter with the optimal size for pages you write.
  • Flush the data in sections so that the user can see partial pages more quickly.
  • Minimize the synchronized block in the service method.
  • Implement the getLastModified() method to use the browser cache and the server cache.
  • Use the application server’s caching facility.
  • Session mechanisms from fastest to slowest are: HttpSession, Hidden fields, Cookies, URL rewriting, the persistency mechanism.
  • Remove HttpSession objects explicitly in your program whenever you finish the session.
  • Set the session time-out value as low as possible.
  • Use transient variables to reduce serialization overheads.
  • Disable the servlet auto reloading feature.
  • Tune the thread pool size.

How to Improve JSP Page performance

  • Use the jspInit() method to cache static data, and release them in the jspDestroy() method.
  • Use the jspInit() method to cache static data.
  • Use StringBuffer rather than using + operator when you concatenate multiple strings.
  • Use the print() method rather than the println() method.
  • Use a ServletOutputStream rather than a PrintWriter to send binary data.
  • Initialize the PrintWriter with the optimal size for pages you write.
  • Flush the data in sections so that the user can see partial pages more quickly.
  • Minimize the synchronized block in the service method.
  • Avoid creating a session object with the directive <%@ page session=”false” %>
  • Increase the buffer size of System.out with the directive <%@ page buffer=”12kb” %>
  • Use the include directive instead of the include action when you want to include another page.
  • Minimize the scope of the ‘useBean’ action.
  • Custom tags incur a performance overhead. Use as few as possible.
  • Use the application server’s caching facility, and the session and application objects (using getAttribute()/setAttribute()). There are also third-party caching tags available.
  • Session mechanisms from fastest to slowest are: session, Hidden fields, Cookies, URL rewriting, the persistency mechanism.
  • Remove ‘session’ objects explicitly in your program whenever you finish the session.
  • Reduce the session time-out as low as possible.
  • Use ‘transient’ variables to reduce serialization overheads.
  • Disable the JSP auto reloading feature.
  • Tune the thread pool size.

Design Patterns : Performance

  • use the flyweight pattern to reduce object creation [The flyweight pattern uses a factory instead of ‘new’ to reuse objects rather than always create new ones].
  • The Singleton pattern and the Flyweight (object factory) pattern are useful to limit numbers of objects of various types and to assist with object reuse and reduce garbage collection.
  • Use the Data Access Object pattern to decouple business logic from data access logic, allowing for optimizations to be made in how data is managed.
  • Use the Fast-Lane Reader pattern to accelerate read-only data access by not using enterprise beans.
  • Use the Front Controller pattern to centralize incoming client requests, allowing optimizations to be made in aggregating the resulting view.
  • Use the Front Controller pattern to channel all client requests through a single decision point, which allows the application to be balanced at runtime.
  • Use the Page-by-Page Iterator pattern to efficiently access a large, remote list by retrieving its elements one sublist of value objects at a time.
  • Use the Session Facade pattern to provide a unified, workflow-oriented interface to a set of enterprise beans, thus minimizing client calls to server EJBs.
  • Use a Session Facade to provide a simple interface to a complex subsystem of enterprise beans, and to reduce network communication requirements
  • Use the Value Object pattern to efficiently transfer remote, fine-grained data by sending a coarse-grained view of the data.
  • Use the factory pattern to enable reuse or cloning of objects
  • The Abstract Factory design pattern uses a single class to create more than one kind of object.
  • An alternative to the Flyweight pattern is the Prototype pattern, which allows polymorphic copies of existing objects. The Object.clone() method signature provides support for the Prototype pattern.
  • Prototypes are useful when object initialization is expensive, and you anticipate few variations on the initialization parameters. Then you could keep already-initialized objects in a table, and clone an existing object instead of expensively creating a new one from scratch.
  • Immutable objects can be returned directly when using Prototyping, avoiding the copying overhead.
  • Combine multiple remote calls for state information into one call using a value object to wrap the data (the Value Object pattern, superceded by local interfaces in EJB 2.0).
  • Where long lists of data are returned by queries, use the Page-by-Page Iterator pattern: a server-side object that holds data on the server and supplies batches of results to the client.
  • When the client would request many small data items which would require many remote calls to satisfy, combine the multiple calls into one call which results in a single Value Object which holds all the data required to be transferred. Use the Value Object to send a single coarse-grained object from the server to the client(s).
  • For read-only access to a set of data that does not change rapidly, use the Fast Lane Reader pattern which bypasses the EJBs and uses a (possibly non-transactional) data access object which encapsulates access to the data. Use the Fast Lane Reader to read data from the server and display all of them in one shot.
  • Wrap multiple entity beans in a session bean to change multiple EJB remote calls into one session bean remote call and several local calls (pattern called SessionFacade).
  • Cache EJBHome references to avoid JNDI lookup overhead (pattern called ServiceLocator).
  • The ServiceLocator/EJBHomeFactory Pattern reduces the expensive JNDI lookup process by caching EJBHome objects.
  • The SessionFacade Pattern reduces network calls by combining accesses to multiple Entity beans into one access to the facade object.
  • The MessageFacade/ServiceActivator Pattern moves method calls into a separate object which can execute asynchronously.
  • The ValueObject Pattern combines remote data into one serializable object, thus reducing the number of network transfers required to access multiple items of remote data.
  • The ValueObjectFactory/ValueObjectAssembler Pattern combines remote data from multiple remote objects into one serializable object, thus reducing the number of network transfers required to access multiple items of remote data.
  • The ValueListHandler Pattern: avoids using multiple Entity beans to access the database, using Data Access Objects which explicitly query the database; and returns the data to the client in batches (which can be terminated) rather than in one big chunk, according to the Page-by-Page Iterator pattern.
  • The CompositeEntity Pattern reduces the number of actual entity beans by wrapping multiple java objects (which could otherwise be Entity beans) into one Entity bean.
  • The Recycler pattern fixes only the broken parts of a failed object, to minimize the replacement cost.
  • Use a factory class instead of directly calling the “new” operator, to allow easier reuse of objects.
  • Instead of making lots of remote requests for data attributes of an object, combine the attributes into another object and send the object to the client. Then the attributes can be queried efficiently locally (this is called the Value Object pattern). Consider caching the value objects where appropriate.
  • The Proxy design pattern
    • Using a proxy, you can delay image loading until the image is required.
    • The Proxy pattern often instantiates its real object, the Decorator pattern (which can also use proxy objects) rarely does.
    • The java.lang.reflect package provides three classes to support the Proxy and Decorator patterns: Proxy, Method, and InvocationHandler.

How to Improve the Java JDBC Performance

  • Use prepared statements. Use parametrized SQL.
  • Tune the SQL to minimize the data returned (e.g. not ‘SELECT *’).
  • Minimize transaction conflicts (e.g. locked rows).
  • Use connection pooling.
  • Try to combine queries and batch updates.
  • Use stored procedures.
  • Cache data to avoid repeated queries.
  • Close resources (Connections, Statements, ResultSets) when finished with.
  • Select the fastest JDBC driver.
  • If you are not using stored procedures or triggers, turn off autocommit. All transaction levels operate faster with autocommit turned off, and doing this means you must code commits.
  • Use connection pooling, either explicitly with your own implementation, or implicitly via a product that supports connection pooling.
  • Use batch updates (sending multiple rows to the database in one call).
  • Use the type-correct get() method, rather than getObject().
  • Avoid n-way database joins: every join has a multiplicative effect on the amount of work the database has to do. The performance degradation may not be noticeable until large datasets are involved.
  • Avoid bringing back thousands of rows of data: this can use a disproportionate amount of resources.
  • Run an application server and any database servers on separate server machines.
  • One of the most time-consuming procedures of a database application is establishing a connection to the database. Use connection pooling to minimize this overhead
  • Use the same connection to execute multiple statements
  • Avoid distributed transactions (transactions that span mutliple connections).
  • Use scrollable ResultSet (JDBC 2.0).
  • Oracle 9i includes queryable snapshots of the main database which can offload the query to run against the clients local snapshot.
  • Performance should be considered at the start of a project
  • Given a simple SQL statement and a stored procedure call that accomplishes the same task, the simple SQL statement will always execute faster because the stored procedure executes the same SQL statement but also has the overhead of the procedure call itself. On the other hand complex tasks requiring several SQL statements can be faster using stored procedures as fewer network trips and data transfers will be needed.
  • Performance can be better if changes to the database are batched: turn off autocommit; add multiple SQL statements using the Statement.addBatch() method; execute Statement.executeBatch().
  • The ConnectionPoolDataSource (from JDBC3.0) and PooledConnection interfaces provide built-in support for connection pools.
  • Use Connection.setReadOnly(true) to optimize read-only database interactions.
  • Use Connection.nativeSQL() to see how the SQL query will execute in the database to help ensure that the SQL is optimized.
  • Use Connection.nativeSQL() to see how the SQL query will execute in the database to help ensure that the SQL is optimized.
  • Commit the data after the transaction completes rather than after each method call
  • Use the fastest driver available to the database: normally type 4 (preferably) or type 3.
  • Use the Page-by-Page Iterator pattern to repeatedly pass small amounts of data rather than huge chunks.
  • Reusing connections allows a prepared statement to be reused.
  • Database connection pools can take one of two strategies: a limited size pool, where attempts to make connections beyond the pool size must wait for a connection to become idle; or a flexible sized pool with a preferred size which removes idle connections as soon as the preferred size is exceeded (i.e. temporarily able to exceed the preferred size). The fixed size pool is generally considered to be the better choice.
  • Avoid the following common mistakes: Failure to close JDBC result sets, statements, and connections; Failure to remove unused stateful session beans; Failure to invalidate HttpSession.
  • CachedRowSet provides cached result sets that do not require continuous connection to the database, allowing connections to be reused more efficiently.
  • Reuse database connections using a connection pool.
  • Obtain and release pooled conections within each method that requires the resource if the connection is very short (termed “Quick Catch-and-Release Strategy” in the article). However do not release the connection only to use it again almost immediately, instead hold the connection until it will not be immediately needed.
  • Response time increasing too much when database is over populated probably indicates lack of or inappropriate indexing on the database.
  • Use SQL clause with EXPLAIN or similar (e.g. “Explain select * from table where tablefield = somevalue”) to ensure that the database is doing an indexed search rather than a linear searches of large datasets.
Follow

Get every new post delivered to your Inbox.

Join 26 other followers