Chapter 6: Database Access Tuning  Server and component transaction settings

Chapter 6: Database Access Tuning

Component design and implementation

The design and implementation of your code to access databases can have a significant effect on performance.

Keep transactions short

Avoid component designs that require the use of long-running transactions. For each transaction that your application runs, the database server may lock tables, rows, indexes, and other resources required to guarantee the required transaction outcome. Long-running transactions reduce the scalability of the application, since the required locks may be held for the duration of the transaction and other users must wait for them to be released.

In EJB components, minimize the use of bean-managed transactions. If you do use bean-managed transactions, avoid implementations that allow the transaction to remain open when a method returns. In stateful components of other types, avoid designs that require transactions to span client method invocations. If the transaction remains open when the business method returns, it can remain open if the client hangs or the user changes their mind. If you cannot avoid these design patterns, configure a transaction timeout as described in “Transaction timeout”.

Many design patterns that depend on long-running transactions can be easily modified to use optimistic concurrency control. That is, rather than running all the database work in one transaction, select the initial values and perform all computations without starting a transaction. Use a timestamp or value comparisons before updates to verify that data has not been modified since it was first selected.

Minimize result set size

Tune your queries and schemas to ensure that you do not waste network resources and memory by selecting unneeded data. For example, do not select 100 rows, then search them in your component to find the one row that you need. Use the query language to direct the database to find and return only the data you need.

When you must return large result sets to the client, you may get better performance by batching the result set into smaller groups of rows, then reassembling them on the client. Doing so avoids the need to construct large TabularResults.ResultSet objects in memory.

Use database server optimizations

Tune your queries to minimize database response time. Take advantage of any performance features available in your database, such as stored procedures if using Sybase Adaptive Server Enterprise. Consult your database performance and tuning documentation. If you are using Sybase Adaptive Server Enterprise, the Performance and Tuning Guide is available on the Sybase Product Manuals Web site.

Minimize use of two-phase commit

Multiple database transactions require two-phase commit, and consequently execute more slowly than those that use only a single database. Review your application design and component transaction settings to make sure that two-phase commit is used only when the component work involved must be part of the same atomic unit of database work.

If a component inherits a transaction in an intercomponent call involving two or more database connections, EAServer uses two-phase commit. The component’s transaction attribute determines whether transactions can be inherited through intercomponent calls. For more information, see “Component properties: Transactions” in the EAServer Programmer’s Guide. For example, two-phase commit is required if the component’s transaction attribute is “Supports,” the component has been called from another component that has attribute “Requires,” and the components use different connection caches.

To avoid use of two-phase commit for a component’s database work, set the transaction attribute to “Requires New” after verifying that the work can be commit or rollback independently of the calling components transaction outcome. If a component performs updates to a noncritical database you can choose “Not Supported” as the component's transaction attribute to eliminate the overhead of using EAServer transactions at all. For example, the component may log usage statistics to a remote database.

If a component requires different transaction attributes for different contexts, you can create a copy of the component definition in EAServer Manager and change only the transaction attribute.

Clean up connections before releasing them to the cache

Many JDBC programs do not explicitly clean up java.sql.Statement objects. Instead, they rely on the JDBC driver to clean up Statement objects when the connection is closed. This strategy does not work with cached connections; you must explicitly clean up Statement objects before releasing a connection back into the cache. To clean up Statement objects, call Statement.close() and set the Statement reference to null.

WARNING! To prevent memory leaks, you must explicitly clean up a connection’s Statement objects before releasing the connection back into the cache. Do not release the same connection more than once.

Avoid unnecessary database work

For PowerBuilder and CORBA components that participate in transactions, you can call isRollBackOnly to test if the transaction is doomed before the method executes more logic that would have to be rolled back. For more information, see “Using transaction state primitives” in the EAServer Programmer’s Guide.

Copyright © 2005. Sybase Inc. All rights reserved. Server and component transaction settings