Performance Enhancements

There are Oracle features to enhance your performance by making database access more efficient. This includes row prefetching, which retrieves query results in groups of rows instead of one at a time and is supported by both Oracle JDBC and Oracle SQLJ. This also includes batch updates (and batch inserts), a feature that sends database updates in batches instead of one at a time. This feature is not currently supported by Oracle SQLJ but is supported by Oracle JDBC, so you can use JDBC code if you require it. (Prepared statements are not cached in SQLJ, instead being re-prepared each time. This effectively disables batch updates.)

For information about JDBC support for row prefetching and batch updates, see the Oracle8i JDBC Developer's Guide and Reference.

Additionally, you can employ the Oracle SQL optimizer for a SQLJ program.

Row Prefetching

Standard JDBC receives the results of a query one row at a time, with each row requiring a separate round trip to the database. Prefetching rows allows you to receive the results more efficiently, in groups of multiple rows each.

To specify the number of rows to prefetch for queries that use a given connection context instance, use the underlying JDBC Connection instance cast to an OracleConnection. Following is an example that sets the prefetch value to 20 for your default connection:

((OracleConnection)DefaultContext.getDefaultContext().getConnection()).setDefaultRowPrefetch(20);
    

        

Each additional connection context instance you use must be set separately, as desired. For example, if ctx is an instance of a declared connection context class, set its prefetch value as follows:

((OracleConnection)ctx.getConnection()).setDefaultRowPrefetch(20);

There is no maximum row-prefetch. The default value is 10 in JDBC, and this is inherited by SQLJ. This appears to be an effective value in typical circumstances, although you may want to increase it if you receive a large number of rows.

See "Prefetch Demo--PrefetchDemo.sqlj" for a sample application showing row-prefetching through SQLJ and insert-batching through JDBC.

Oracle SQL Optimizer

Oracle SQL allows you to tune your SQL statements by using /*+ or --+ comment notation to pass hints to the Oracle SQL optimizer. The SQLJ translator recognizes and supports these optimizer hints, passing them at runtime to the database as part of your SQL statement.

You can also define cost and selectivity information for a SQLJ stored function, as for any other stored function, using the extensibility features for SQL optimization in Oracle8i. During SQL execution, the optimizer invokes the cost and selectivity methods for the stored function, evaluates alternate strategies for execution, and chooses an efficient execution plan.

For information about the Oracle optimizer, see the Oracle8i SQL Reference.

---