Database and SQL Tuning

For a vast majority of WebLogic applications, database access is a critical piece of functionality and potentially a good source for performance tuning. For example, there can be issues with the number of database accesses that an application is making, which might encourage an application developer to cache in memory values that don't change frequently, instead of retrieving them each time. Or there could be an issue with how long a particular query is taking to return, which might be improved by adding indexes or using temporary tables on the database. There are several other factors concerning the general performance of the database itself as well as WebLogic's communication with the database, which we will now examine more closely.

Connection Pools

Compared to actually executing an SQL statement, establishing a connection to the database is a very slow operation. Connection pools allow WebLogic Server to be in charge of establishing connections to the RDBMS and distributing those connections to WebLogic Server applications as they are needed. Additionally, connection pools can be set up to automatically grow and shrink depending on application needs. Increasing the initial number of connections in the pool will make WebLogic Server slower to start, but applications will not have to wait when requesting a database connection. Ideally, the best performance will be achieved when the number of connections in the pool is equal to the number of connections needed by WebLogic applications.

Caching Prepared Statements

Each JDBC connection pool can potentially cache SQL prepared statements. When a WebLogic application calls a prepared statement that is in the cache, the database does not have to parse the SQL. This reduces the load on the database and leaves more CPU cycles for other tasks. By default, the number of prepared statements the cache can hold is set to 0. Change this number, benchmark your application, and observe how it affects performance. If your application uses prepared statements, chances are that you will see an improvement. There are some restrictions on how this cache can be used. Please see http://e-docs.bea.com/wls/docs81/jdbc/performance.html for more information.

JDBC Driver Type

The type of JDBC driver will affect performance. Database connections using Type 1 drivers must incur the cost of going through an ODBC driver as well, whereas Type 4 drivers tend to perform the best because communication with the database is direct and there is no need to go through ODBC or a native interface.

Database Schema Design

In many cases, our WebLogic applications must use an existing database schema and attempt to get the best performance from it. In other cases, we have the opportunity to design the schema with our WebLogic application in mind. If this is the case, make sure that you design with performance in mind. Although using normal form in database design represents a popular mindset, you must also consider the performance costs of the database schema you ultimately decide to use.

Using Correct Indexes

SQL statements are executed by the database in a variety of ways each with an associated performance cost. For example, when a SELECT statement is executed and the WHERE clause is based on columns for which there are no indexes, the database must perform a full table scan. This means that every row in a database table must be examined to determine whether it meets the WHERE clause criteria. If the table has many rows, performance will suffer and your RDBMS will be working harder than it needs to. Creating additional indexes on these columns will improve performance on these queries and throughput of the database as a whole.

Using Stored Procedures

Although using stored procedures ties your code to a particular version of SQL and, ultimately, to a database vendor, they can potentially improve the performance of your SQL related code. However, by putting business logic in the database in addition to the middle tier, you are potentially making your application harder to maintain. You need to find the correct balance among performance, maintainability, and portability that meets both performance requirements and company policies towards software design. An interesting note is that Oracle9i allows stored procedures to be created in Java, thus gaining performance and keeping business logic in Java.

Reducing the Number of Round Trips

If your WebLogic application performs more round trips to the database, performance will suffer. In some cases, it might be possible to cache database updates until a later time and then perform all of them with one trip to the database. This practically removes the overhead associated with network communications (assuming that your database is running on a separate machine). Also, if it is possible to execute all of these updates in a single transaction, the database will be able to process them faster than using separate transactions for each update because of the underlying database locks associated with transactions.

Caching Data in the Middle Tier

If information from the database is static or changes infrequently, consider caching it at the middle tier to avoid extra trips to the database. This can be achieved with read-only Entity EJBs or by binding your custom data class to the JNDI tree.

Smart SQL

Using SQL smartly simply means that performance should be a priority when designing the SQL statements in your application. There is usually more than one way of implementing desired functionality. Some issues to consider are whether or not to use correlated sub-queries and temporary tables to improve performance. Because there is no right answer for every situation, the best way to know which techniques run faster is by benchmarking. Another thing to consider is moving older, less frequently used information into historical tables to reduce the load on the database when this information is not needed.

Enterprise JavaBean Tuning

One of the most common enemies of good performance in WebLogic applications is incorrect usage of EJBs. When used correctly, EJBs can help make applications much easier to build and maintain. But when applied incorrectly, they can create additional performance overhead that can slow down a system tremendously. We will discuss some common approaches to avoiding this pitfall.

Using a Session Façade Pattern

Direct method calls to EJBs from servlets, JSPs, and thick clients involve the overhead of network communications that can be hundreds of times more costly than a local method call. In an effort to improve performance, do not have these clients access entity EJBs directly; rather, use a Stateless Session EJB as a mediator to an Entity EJB. The Stateless Session EJB can then access the Entity EJB using a local interface that acts like a local method call and does not incur any network communications overhead. This design pattern also increases maintainability by providing a layer of abstraction and makes it easier to handle changes in the Entity bean's methods.

Using Coarse-Grained Methods and Value Objects

Create a value object that can hold every attribute of an Entity EJB. A value object is a JavaBean with get and set methods. Then, create an Entity EJB coarse-grained method that will accept this value object. In this way, a client can set all the Entity EJB attributes with one method call. Similarly, create a coarse-grained method that will return a value object so that a client can query all the Entity EJB attributes with one method call.

Tuning Deployment Descriptor Parameters

WebLogic Server supports several EJB tuning parameters that can be defined in the deployment descriptor, weblogic-ejb-jar.xml. One such parameter, entity-cache, an application-level cache of Entity beans. This pool of beans can speed access to various data by eliminating database round-trips. It has several parameters that can be tuned, including the maximum size of the cache and timeout values for refreshing the cache. Try different values for these options depending on your application.

One parameter of the entity-cache element that deserves further discussion is concurrency-strategy. This property defines how WebLogic handles concurrent access to an Entity bean held in the cache. This value may be one of "Exclusive", "Database", "ReadOnly", or "Optimistic". "Exclusive" and "Database" guarantee that only one transaction at a time will be able to write to an entity bean, and in so doing incur the largest performance overhead. If static data is being read as an Entity bean, it should be marked as "ReadOnly", which is much faster because the object never needs to be updated in the cache after it's been read in. If the value is set to "Optimistic", the EJB container makes sure that any data being updated by the transaction has not changed since the transaction started. It does this by comparing fields in the Entity EJB and the database. If something has changed, the EJB container rolls back the transaction. The theory is that if two objects have a reference to a bean, the container optimistically assumes that only one of them will actually be modifying it at a given time. By reducing the amount of time that database locks are held, performance can be increased.

Another area where performance can be tuned is with transaction isolation levels. The isolation-level element of the transaction-isolation element is used to declare the isolation level of an entity or session bean on a per-method basis. This value can be one of "TransactionSerializable", "TransactionReadCommitted", "TransactionReadUncommitted", and "TransactionRepeatableRead". If you are willing to make a trade-off between data consistency and performance, consider using "TransactionReadUncommitted". This value, sometimes referred to as a dirty read, allows the transaction to see updated by uncommitted data from other transactions. Because it doesn't have to lock access to that data during another transaction's update, performance can be increased.

The transaction granularity is also something that should be considered for performance reasons. For example, if several Entity beans are to be updated within one Session bean method, it should typically be created as one transaction for all, as opposed to an individual transaction for each. This is primarily to reduce the overhead of acquiring transaction contexts. But it usually makes sense from an application standpoint as well, where you'd want either all or none of your changes in a given method to be committed to preserve data integrity.

Performance Testing EJBs

Bean-test from Empirix is able to work with WebLogic Server and can simulate client load, perform stress testing, and pinpoint performance issues in EJBs. Go to http://www.empirix.com for more information about Bean-test.