Heterogeneous Batch in IBM Optim pureQuery

Optim pureQuery provides many benefits which include improved quality, improved developer productivity and dramatically improved performance over JDBC based solutions against databases which support static package binds, such as DB2.

Most often discussed about the performance benefits is static packages. However, if you have high-volume INSERT and UPDATE applications, static SQL is only a part of the benefit. pureQuery offers a Heterogeneous Batch capability which can significantly reduce the number of line turns.

As you probably know, JDBC provides a both a Homogeneous Batch (where all of the statements in the batch are exactly the same) and Heterogeneous Batch capability (where there can be different statements which perform different operations or go against different tables), but there is a big catch. While the Homogeneous Batch capability is on PreparedStatement(), the Heterogeneous Batch is actually on Statement(). Statement() of course doesn't support parameter markers, so any variables required for a statement have to be literal values! It is obvious then that Statement Cache will be generally useless, unless of course you either have no variables or there are variables but a relatively small number of variations, because each set of literal values helps to create a unique SQL statement in cache. So for JDBC, Heterogeneous Batch should be a non-starter for the majority of requirements.

pureQuery provides a Heterogeneous Batch capability which allows parameter makers, so if you are executing statements via pureQuery dynamically, then Statement Cache will be effective. If you are executing statements statically, then all the better. You would get the benefit of fewer line turns as well as static SQL execution.

Heterogeneous Batch is available currently only for update type operations (INSERT, UPDATE and DELETE). It is capable of utilizing Multi-Row INSERT (MRI), just as Homogeneous Batch would and it can contain different statements as well as zero to many Homogeneous Batches.

So how important would this be to your applications? It depends. How often do you perform operations in a transaction that require you to execute multiple update-type statements? For instance, if you have a account management application at a bank which creates new accounts? Frequently, there would be multiple relational tables with instances which must be constructed for a valid account. With Heterogeneous Batch, you can create a batch object, build it up with many statements and then end the batch and it will execute whatever statements were within the batch in one trip to the database. The more create, update or delete (CUD) operations you do in a batch, the more savings you will realize.

Beyond the line-turns and static execution, there is also a chance perhaps to simplify your existing processes. For instance, maybe you already know you should reduce the number of calls to the DBMS for creating a bank account, and the way you achieved this was through a Stored Procedure which contains all of the CUD statements. Most likely, there is complexity (and perhaps great complexity) in how the input data needed for the operations is passed to the statements in the Stored Procedure. Sometimes is maybe in a large VARCHAR or a LOB. This usually involves logic which has to be maintained on both the caller and called side of the SP invocation It is much easier and far less maintenance to simply create a Heterogeneous Batch directly in your Java class.

As always, the more volume you have in your applications, the more it matters. Seek to include Heterogeneous Batch where it makes sense in new applications or even in legacy applications when an opportunity presents itself to rewrite a component. Also, when you are tuning an application, keep Heterogeneous Batch in mind as a potential tool in your arsenal which could reduce CPU consumption, reduce elapsed time and reduce contention potential alll at once. In synchronous or asynchronous, OLTP or MDB driven processing, anything can benefit. Even if you only have 2 statements in a batch, you will save yourself half of of the calls to the database!

Check out the documentation for Heterogeneous Batch in Information Center.

And if you have doubts about the benefits in your situation, try to create a prototype and compare.

Views: 158

Tags: Batch, CPU, Heterogeneous, Homogeneous, high-volume, line-turn, performance, pureQuery


You need to be a member of ChannelDB2 to add comments!

Join ChannelDB2

Comment by Kathy on December 11, 2009 at 4:57pm
Hey, Dan. Glad to see you blogging about pureQuery.

Try BLU Acceleration on Cloud

© 2014   Created by channeldb2.

Badges  |  Report an Issue  |  Terms of Service