Best method of using database sequences from BPEL; native sequencing or Tapi?

Introduction

In this example I will create a simple BPEL process; a Data Service, which will insert a record in a table and return the Id of the created entry. I will compare two methods to achieve this

1 TAPI (Table API) + before insert trigger for Id generation

2 Direct insert from BPEL DbAdapter and using native sequencing for Id generation

I will (in general terms) compare both methods on the amount of developer time required to achieve the method and the maintainability. I will also do a performance test to compare both methods.

I've used the following VM for this test; http://www.oracle.com/technetwork/middleware/soasuite/learnmore/vmsoa-172279.html. I've created a database user testuser. In the Weblogic console I've created a JDBC datasource and ConnectionFactory. Also I've created two simple synchronous BPEL processes which have the same activities to make sure the overhead for the processes is equal. These processes can be downloaded here; https://dl.dropbox.com/u/6693935/blog/TestSequencing.zip

Implementation

TAPI + before insert trigger

I created a simple table, sequence and a before insert trigger;

CREATE SEQUENCE "TESTUSER"."TESTTEXT_SEQ" MINVALUE 1 INCREMENT BY 1 START WITH 1;
/
CREATE TABLE "TESTUSER"."TESTTEXT"
  (
    "ID"   NUMBER NOT NULL ENABLE,
    "TEXT" VARCHAR2(256 BYTE),
    CONSTRAINT "TESTTEXT_PK" PRIMARY KEY ("ID") ENABLE
  );
 /
CREATE OR REPLACE TRIGGER "TESTUSER"."BI_TESTTEXT" BEFORE
  INSERT ON TESTTEXT FOR EACH ROW BEGIN IF (:NEW.ID IS NULL) THEN
  SELECT TESTTEXT_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
END IF;
END;
/
ALTER TRIGGER "TESTUSER"."BI_TESTTEXT" ENABLE;

I created a TAPI for the table as followed;

The result was;

CREATE OR REPLACE
PACKAGE TESTTEXT_tapi
IS
type TESTTEXT_tapi_rec
IS
  record
  (
    TEXT TESTTEXT.TEXT%type ,
    ID TESTTEXT.ID%type );
type TESTTEXT_tapi_tab
IS
  TABLE OF TESTTEXT_tapi_rec;
  -- insert
PROCEDURE ins(
    p_TEXT IN TESTTEXT.TEXT%type DEFAULT NULL ,
    p_ID   IN TESTTEXT.ID%type );
  -- update
PROCEDURE upd(
    p_TEXT IN TESTTEXT.TEXT%type DEFAULT NULL ,
    p_ID   IN TESTTEXT.ID%type );
  -- delete
PROCEDURE del(
    p_ID IN TESTTEXT.ID%type );
END TESTTEXT_tapi;
/
CREATE OR REPLACE
PACKAGE body TESTTEXT_tapi
IS
  -- insert
PROCEDURE ins(
    p_TEXT IN TESTTEXT.TEXT%type DEFAULT NULL ,
    p_ID   IN TESTTEXT.ID%type )
IS
BEGIN
  INSERT INTO TESTTEXT
    ( TEXT ,ID
    ) VALUES
    ( p_TEXT ,p_ID
    );
END;
-- update
PROCEDURE upd
  (
    p_TEXT IN TESTTEXT.TEXT%type DEFAULT NULL ,
    p_ID   IN TESTTEXT.ID%type
  )
IS
BEGIN
  UPDATE TESTTEXT SET TEXT = p_TEXT WHERE ID = p_ID;
END;
-- del
PROCEDURE del(
    p_ID IN TESTTEXT.ID%type )
IS
BEGIN
  DELETE FROM TESTTEXT WHERE ID = p_ID;
END;
END TESTTEXT_tapi;

This TAPI was insufficient for my test since there was no link to the sequence used and no procedure/function returning the Id. It would be nice if Oracle could enhance this feature a little in the future to make it more flexible. SQL Developer however should not become a new 'Headstart'! (remember this product from the Designer days?)

For the purpose of this test I've updated the insert procedure to;

PROCEDURE ins(
    p_TEXT IN TESTTEXT.TEXT%type DEFAULT NULL ,
    p_ID   IN OUT TESTTEXT.ID%type )
IS
BEGIN
  INSERT INTO TESTTEXT
    ( TEXT ,ID
    ) VALUES
    ( p_TEXT ,p_ID
    ) returning id into p_ID;
END;

Direct insert from BPEL DbAdapter and using native sequencing

In order to use native sequencing, you have to specify the sequence in the DbAdapter wizard in JDeveloper;

Also when you want to use native sequencing, the sequencePreallicationSize (DbAdapter, connectionfactory setting) needs to be equal to the increment of the sequence;

If you don't do this, you will encounter the following error;

java.lang.Exception: oracle.sysman.emSDK.webservices.wsdlapi.SoapTestException: Client received SOAP Fault from server : Exception occured when binding was invoked. Exception occured during invocation of JCA binding: "JCA Binding execute of Reference operation 'insert' failed due to: DBWriteInteractionSpec Execute Failed Exception. insert failed. Descriptor name: [insert_DB.Testtext]. Caused by Exception [EclipseLink-7027] (Eclipse Persistence Services - 2.3.1.v20111018-r10243): org.eclipse.persistence.exceptions.ValidationException Exception Description: The sequence named [TESTTEXT_SEQ] is setup incorrectly. Its increment does not match its pre-allocation size.. Please see the logs for the full DBAdapter logging output prior to this exception. This exception is considered not retriable, likely due to a modelling mistake. ". The invoked JCA adapter raised a resource exception. Please examine the above error message carefully to determine a resolution

Mind that when this error occurs, the sequence is already increased however no entry is saved in the table!

Performance test

To make the test more representative, I've disabled the database trigger for the native sequencing test. After each test, I've truncated the table and done drastic cleaning on the dehydration store as described on; http://www.emarcel.com/soa-suit/152-deleteinstancessoasuite11gwls. I've done a SOAP UI loadtest with the following settings; 5 threads, no spread/random, one message every 100 ms per thread and a run for 60 seconds.

Results native sequencing

Results TAPI + before insert trigger

Conclusion

Performance

As can be seen from the performance test, using a TAPI is significantly faster then using native sequencing. More requests can be processed in the same time and the average response time is less.

Maintainability

By using a TAPI, the database table and the BPEL process are not tightly coupled. This increases flexibility. For example, it would be easy to implement logging in the TAPI or handle changes in the table layout without effecting the BPEL process using the TAPI. There is however more (database) code involved. This code needs to be maintained.

Work required

When using a TAPI, the base can be generated and small changes will need to be made in order to make it useful code. When using native sequencing, no database procedures are required; less code is used. The logic concerning the sequence is handled by the DbAdapter instead of database code. Do not forget to set the sequencePreallocationSize property on the applicationserver.

Overall

Using a TAPI provides best performance and flexibility. However it requires more work to create and there will be more code to maintain. Using native sequencing, the amount of database code can be limited; the DbAdapter will handle the sequence part.