Performance

You can use several techniques to improve data access performance for your application. This section discusses some of the more common approaches, including:

Data Optimization
Batch Writing
Binding and Parameterized SQL
Prepared Statement Caching

Data Optimization

By default, OracleAS TopLink optimizes data access by accessing the data from JDBC in the format the application requires. For example, OracleAS TopLink retrieves longs from JDBC instead of having the driver return a BigDecimal that OracleAS TopLink would then have to convert into a long.

OracleAS TopLink also retrieves dates as strings and converts directly to the date or Calendar type used by the application. Some older drivers do not convert data correctly. For example, earlier BEA WebLogic JDBC drivers cannot convert dates to strings in the correct format. If you use one of these drivers, disable data optimization.

Note:

The problems mentioned here may have been fixed in more recent versions of the drivers. See your vendor documentation for relevant updates.

Example 5-10 Disabling Data Optimization in Code

session.getLogin().dontOptimizeDataConversion() ;

Example 5-11 Disabling Data Optimization in the sessions.xml File

<login>
    ...
    <should_optimize_data_conversion>false</should-optimize-data-conversion>
</login>

Batch Writing

Batch writing can improve database performance by sending groups of INSERT, UPDATE, and DELETE statements to the database in a single transaction, rather than individually. OracleAS TopLink supports batch writing for selected databases and for JDBC 2.0 batch-compliant drivers.

To enable JDBC 2.0 batch writing, i nvoke the useBatchWriting() method on the login.

If you use a JDBC driver that does not support batch writing directly, you can still take advantage of batch writing, because OracleAS TopLink provides its own batch writing functionality. To enable OracleAS TopLink batch writing support, run the code in Example 5-12.

Example 5-12 Batch Writing

project.getLogin().useBatchWriting();
project.getLogin().dontUseJDBCBatchWriting();

For more information about batch writing, see Chapter 10, "Tuning for Performance".

Binding and Parameterized SQL

By default, OracleAS TopLink prints data inlined into its generated SQL and does not use parameterized SQL . However, you can implement parameterized SQL to:

Alleviate the limit imposed by some drivers on the size of the data to be printed.
Cache prepared statements to improve performance.

OracleAS TopLink does not implement parameterized SQL because many JDBC drivers do not fully support parameter binding, and have size or type limits.

For more information about binding and binding size limits, see your database documentation.

If your driver supports parameter binding and also imposes a limit on the size of the printable results, use parameter binding to accommodate large binary data in one of the following ways:

Call the useByteArrayBinding() method. This is a common method to accommodate large binary data.
If you use a JDBC driver that is more efficient at reading large binary data through streams, call the useStreamsForBinding() method.
Configure binding for large string data with the useStringBinding() method.

Example 5-13 Using Parameter Binding with Large Binary Data

project.getLogin().useByteArrayBinding();
project.getLogin().useStreamsForBinding();
project.getLogin().useStringBinding(50);
project.getLogin().bindAllParameters();
project.getLogin().cacheAllStatements();
project.getLogin().setStatementCacheSize(50);

Prepared Statement Caching

OracleAS TopLink enables you to cache JDBC-prepared statements to improve query performance. Prepared statements improve database performance by reducing the number of times the database SQL engine parses and prepares a SQL call for a frequently called query.

To enable prepared statement caching, cache the statement and bind its parameters. You can do this at the query level or at the session level.

Prepared Statement Caching for a Query

To cache the prepared statement for an individual query, configure statement caching in the query definition before executing the query. You can do this either in Java code or as part of the SQL for a named query in OracleAS TopLink Mapping Workbench.

Example 5-14 Caching a Prepared Statement in Code for an Individual Query

// Add a query.
ExpressionBuilder builder = new ExpressionBuilder();
ReadAllQuery query = new ReadAllQuery(PhoneNumber.class, builder);

Expression exp = builder.get("id").equal(builder.getParameter("ID"));	
query.setSelectionCriteria(exp.and(builder.get("areaCode").equal("613")));

query.addArgument("ID");

/* The following options force OracleAS TopLink to cache the prepared statement and bind any arguments required by the query */
query.cacheStatement();
query.bindAllParameters();

descriptor.getQueryManager().addQuery("localNumbers", query);

Prepared Statement Caching for a Session

To cache all prepared statements for a session, edit the sessions.xml file in the OracleAS TopLink Sessions Editor, adding login options to bind all parameters and cache statements.

Example 5-15 Caching Prepared Statements in the sessions.xml File

<session>
    ...
    <login>
        ...
        <should-bind-all-parameters>true</should-bind-all-parameters>
        <should-cache-all-statements>true</should-cache-all-statements>
    </login>
    ....
</session>

Failure to execute after a loss of communication to the database

Prepared statements may fail to execute after a loss of communication to the database. If you configure a login or query to use statement caching, and communication with the database is lost and then restored, previously cached statements may fail to execute. For example, it is a common practice to define an exception handler and register it with a Session using Session.setExceptionHandler(). When the exception handler is invoked to handle a loss of communication and the exception handler re-establishes the connection to the database, any attempt to re-execute a previously cached statement will fail.