Data Access Optimization

Depending on the type of data source your application accesses, TopLink offers a variety of Login options that you can use to tune the performance of low level data reads and writes.

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

• JDBC Driver Properties Optimization

• Data Format Optimization

• Batch Writing

• Parameterized SQL (Binding) and Prepared Statement Caching

JDBC Driver Properties Optimization

Consider the default behavior of the JDBC driver you choose for your application. Some JDBC driver options can affect data access performance.

Some important JDBC driver properties can be configured directly using TopLink Workbench or TopLink API (for example, see "JDBC Fetch Size").

JDBC driver properties that are not supported directly by TopLink Workbench or TopLink API can still be configured as generic JDBC properties that TopLink passes to the JDBC driver.

For example, some JDBC drivers, such as Sybase JConnect, perform a database round trip to test whether or not a connection is closed: that is, calling the JDBC driver method isClosed results in a stored procedure call or SQL select. This database round-trip can cause a significant performance reduction. To avoid this, you can disable this behavior: for Sybase JConnect, you can set property name CLOSED_TEST to value INTERNAL.

For more information about configuring general JDBC driver properties from within your TopLink application, see "Configuring Properties".

Data Format Optimization

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

Some older JDBC drivers do not perform data conversion correctly and conflict with this optimization. In this case, you can disable this optimization (see "Configuring Advanced Options").

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.

When used without parameterized SQL, this is known as dynamic batch writing.

When used with parameterized SQL (see "Parameterized SQL (Binding) and Prepared Statement Caching"), this is known as parameterized batch writing. This allows a repeatedly executed statement, such as a group of inserts of the same type, to be executed as a single statement and a set of bind parameters. This can provide a large performance benefit as the database does not have to parse the batch.

When using batch writing, you can tune the maximum batch writing size using setMaxBatchWritingSize method of the Login interface. The meaning of this value depends on whether or not you are using parameterized SQL:

• If you are using parameterized SQL (you configure your Login by calling Login method bindAllParameters), the maximum batch writing size is the number of statements to batch (default: 100).

• If you are using dynamic SQL, the maximum batch writing size is the size of the SQL string buffer in characters (default: 32000).

By default, TopLink does not enable batch writing because not all databases and JDBC drivers support it. Oracle recommends that you enable batch writing for selected databases and JDBC drivers that support this option (see "Configuring JDBC Options").

For a more detailed example of using batch writing to optimize write queries, see "Batch Writing and Parameterized SQL".

Parameterized SQL (Binding) and Prepared Statement Caching

Using parameterized SQL, you can keep the overall length of an SQL query from exceeding the statement length limit that your JDBC driver or database server imposes.

Using parameterized SQL and prepared statement caching, you can improve performance by reducing the number of times the database SQL engine parses and prepares SQL for a frequently called query.

By default, TopLink does not enable parameterized SQL and prepared statement caching, because not all databases and JDBC drivers support it. Oracle recommends that you enable parameterized SQL and prepared statement caching for selected databases and JDBC drivers that support these options.

Not all JDBC drivers support all JDBC binding options (see "Configuring JDBC Options"). Selecting a combination of options may result in different behavior from one driver to another. Before selecting JDBC options, consult your JDBC driver documentation. When choosing binding options, consider the following approach:

1. Try binding all parameters with all other binding options disabled.

2. If this fails to bind some large parameters, consider enabling one of the following options, depending on the parameter's data type and the binding options that your JDBC driver supports:

   1. To bind large String parameters, try enabling string binding.

      If large String parameters still fail to bind, consider adjusting the maximum String size. TopLink sets the maximum String size to 32000 characters by default.

   2. To bind large Byte array parameters, try enabling byte array binding.

3. If this fails to bind some large parameters, try enabling streams for binding.

   Typically, configuring string or byte array binding will invoke streams for binding. If not, explicitly configuring streams for binding may help.

For J2EE applications that use TopLink external connection pools, you must configure parameterized SQL in TopLink but you cannot configure prepared statement caching in TopLink. In this case, you must configure prepared statement caching in the application server connection pool. For example, in OC4J, if you configure your data-source.xml file with an emulated data-source (where connection-driver is oracle.jdbc.OracleDriver and class is oracle.j2ee.sql.DriverManagerDataSource), you can configure a non-zero stmt-cache-size that enables JDBC statement caching and defines the maximum number of statements cached.

For applications that use TopLink internal connection pools, you can configure parameterized SQL and prepared statement caching.

You can configure parameterized SQL and prepared statement caching at the:

• project level–applies to all named queries (see "Configuring Named Query Parameterized SQL and Statement Caching at the Project Level")

• descriptor level–applies on a per-named-query basis (see "Configuring Named Query Options")

• database login level–applies to all queries (see "Configuring JDBC Options") and provides additional parameter binding API to alleviate the limit imposed by some drivers on SQL statement size.

• query level–applies on a per-query basis (see "Using Parameterized SQL and Statement Caching in a DatabaseQuery").