Using DirectSQL in BPEL / ESB Database Adapter

If you read the Database Adapters User's Guide you will sooner or later get to the Performance section, and there you will find DirectSQLPerformance briefly mentioned. However, it is not described in details, so here are some additional comments on this feature.


The Default Behaviour

The normal way for the Database Adapter to work is to use TopLink between the adapter and the Database. This is transparent to the end user when creating a database adapter in either ESB or BPEL. The only hint that you will get that TopLink is involved is in your source project. Here you will find a generated TopLink mapping file and some additional classes used by TopLink within your project. In most cases you will not have to worry about this at all. TopLink behaves like a good citizen within your process, and things work fine.


What is DirectSQL?

This is a feature of the Database Adapter that let it bypass the TopLink framework, and instead use direct JDBC SQL calls to the database. Well, it will not totally bypass TopLink, it will still be used for generating the SQL, obtaining connections, and table introspection. However, other functions of TopLink (for example the cache) will not be used under DirectSQL.

So, why bother about DirectSQL at all? Well it can, under some circumstances, give you better performance. I have found that it is very hard to identify these circumstances and predict when it will and when it won't improve the performance. The advice is basically just to test it, and see if improves the performance or not.


What are the Gotchas?

There are some requirements that need to be fulfilled in order for this feature to work. If you have configured DirectSQL, but some of the requirements are not fulfilled, the adapter will fallback and use TopLink. It will in these cases also log a warning message why it didn't work.

The restrictions that needs to be taken into account are listed below:

• For an Inbound Adapter you must have DeletePollingStrategy.
• For an Outbound Adapter you can only use it with Insert.
• It only works for flat table structures.
• It is limited to work with String, Number, Clob, Blob and Date & Time Types only.
• It does not work with the DetectOmissions feature.

How is it Configured?

It is configured in the adapter WSDL file:

<jca:operation
InteractionSpec="oracle.tip.adapter.db.DBWriteInteractionSpec"
DescriptorName="myService.PerfOut"
DmlType="insert"
DetectOmissions="false"
UseDirectSql="true"
OptimizeMerge="true"
MappingsMetaDataURL="myService_toplink_mappings.xml" />

Note that you in addition to setting UseDirectSql="true" you must also set DetectOmissions="false", this because DetectOmissions defaults to true.