Database access is traditionally a performance bottleneck in enterprise applications. In addition to the connection-establishment and data-transfer dimensions of the problem, there's also the fact that in order for one seemingly simple SQL statement to execute, a large amount of "back end" code has to execute in order to parse the SQL and compile it into a form that can be used. Under the covers, a database access is no trivial matter.

Until recently, there wasn't much you could do in an xCommerce JDBC Component to improve performance, outside of using connection pooling. But that's changed as of xCommerce version 2.7. With the most recent release of xCommerce, you've got two powerful new weapons in the battle for better JDBC performance: prepared statements, and SQL batching.

As of version 2.7, the xCommerce Enterprise Enabler for JDBC has the ability to convert your SQL statements to JDBC PreparedStatement objects. What this means is that the RDBMS precompiles the statement in such a way that it can be cached in memory, so that when the same command executes over and over again (for example, in a loop), the cached SQL expression can be reused, with new argument values inserted as need be. This can be a significant performance optimization in cases where statements execute in a loop.

You can designate any SQL statement as a "prepared statement" using the checkbox provided in the JDBC Component Editor's Query/Result Mapping Pane, just above the SQL edit box:

By default, this checkbox is unchecked. For SQL Statement actions that are executed only once in the course of a service's lifetime, you should leave the checkbox unchecked. But for statements inside loops, you should check the checkbox. At the very least, you should do some performance testing with and without the box checked, to see what the overall effect is for the component in question. (The actual performance increase will obviously vary from one component to the next, depending on what the component is doing and the degree to which performance is data-bottlenecked.)

Checking the Execute as Prepared checkbox means xCommerce will send the SQL statement to the database separate from the parameters that will be used with the statement. When the statement is received by the DBMS, it is compiled and cached, so that it doesn't have to be sent again in a subsequent iteration of a loop. Only the parameter values need be sent.

An example of using "Execute as Prepared" on a statement inside a loop is shown in the action model depicted below.

Prepared statements can be used for SQL statements that take no parameters, but as a practical matter you will probably use prepared statements most often for SQL expressions that take parameters.

Much of the performance increase attributable to the use of prepared statements can also be gotten through the use of stored procedures. However, stored procedures usually encompass larger units of work and often involve SQL syntax that is vendor-specific. With the "Execute as Prepared" checkbox in xCommerce, you get the performance advantages of stored procedures, but at a more granular level, and without the syntax intricacies.

Most database drivers allow batch execution of SQL statements in order to minimize demand on connection resources. For example, a user may want to insert data into a table in one database and delete data from a table in another database, all in one round trip. This is possible with the SQL Batch action, which is new for xCommerce 2.7.

SQL Batch actions allow you to specify that a particular group of SQL Statement actions should be accumulated into a single batch and transmitted to the database as a unit

The Batch action is actually three separate actions: Start Batch, Execute Batch, and Discard Batch. These commands can be accessed from the context menu in the JDBC Component (or from the main menu bar); see below.

 Selecting SQL Batch causes the following dialog to appear:

 Start Batch

You must tell xCommerce where the beginning of a batch occurs, by placing a Start Batch statement before the first SQL Statement in a series of statements that you want to group. This command sets a checkpoint for rollback purposes (in case the batch does not finish normally). From the first occurrence of a Start Batch command until the next occurrence of an Execute Batch command, SQL Statements are merely accumulated, rather than executed.

Execute Batch

Execution of a batch does not occur until an Execute Batch command is reached. An Execute Batch statement can be placed immediately after a batch of SQL Statement actions, or it can be placed at some point downstream of the batched actions (such as in one branch of a Decision action). In other words, you can create a batch in one location and execute it, conditionally, from another location in your action model. The Execute Batch can also come after any number of intervening non-SQL actions (such as Map or Function actions), but you should be aware that those actions will execute before the SQL Statements in your batch, because the batch cannot execute until the Execute Batch is reached. Therefore, you should not create logical dependencies between upstream and downstream actions within a single batch.

Because a batch executes in one round trip (it doesn't "return" anything between SQL calls), you must not use SELECT operations inside batches. A batch should include only INSERT, DELETE, and UPDATE statements.

Discard Batch

The Discard Batch command is a memory-deallocating command that causes the previously held batch to go out of scope. Ordinarily, when an SQL batch executes without error, the batch is discarded automatically after it executes and there is no need to issue an explicit discard.  You will use Discard Batch only when you have an action model that contains two or more sequential SQL batches (each with its own Execute Batch command) wrapped in Try/On Error statements.

The need for a Discard Batch arises when an upstream batch executes abnormally (generating an exception). In order to continue to another batch, you need to purge the previous batch from memory (with an explicit Discard Batch in the On Error branch of your "Try" action). Failure to use Discard Batch under these conditions would cause the next Start Batch to throw an exception. This scenario is illustrated below.

In the case depicted above, where there are two SQL batches (each enclosed in a Try/On Error action), failure to include a Discard Batch action in the error branch of the first Try will cause the next Start Batch to throw an exception (assuming the first batch fails). The moral: When two or more batches will execute sequentially, wrap each in a Try/On Error action and include a Discard Batch command in the On Error branch of each. For action models in which there is only a single SQL batch, Discard Batch is not necessary. After normal execution of a (single) batch, memory allocated to the batch is released automatically; and if the batch returns an error, the batch will go out of scope (and be garbage-collected) when the component itself goes out of scope.

It's usually impossible to know whether using Prepared Statements will give more performance than using batched SQL actions without doing a bit of testing. Therefore, in your next xCommerce service that uses a JDBC Component, try each strategy and benchmark the results. Also, be sure to test the deployed service (with appropriate traffic loads) on the server, since pool-related performance issues come into play at runtime, and those factors are impossible to test at design time.

To learn more about how JDBC implements prepared statements, see the excellent overview at http://java.sun.com/docs/books/tutorial/jdbc/basics/prepared.html. For additional information on Batch actions, see the documentation that ships with the xCommerce 2.7 Enterprise Enabler for JDBC. For information on how to obtain xCommerce 2.7, contact the eBusiness Integration Products Division for details at ebizintegration@silverstream.com