Thursday, November 17, 2011

Optimized update insert ADF ?

I had a recently asked a question on bulk uploads in OTN forum. Seems the performance that i would receive for bulk update/insert scenarios isn't at par.
So i created the procedure and a custom object type and table of that object type which provides far better performance.

Let's say your table structure is as follows :-

--     TXN_TBL
("TXN_ID" Number,
"USER_NAME" VARCHAR2(50 BYTE),
"TXN_DATE" DATE,
"TXN_AMOUNT" NUMBER)

So you can basically create a object type mirroring that structure as follows
create or replace type TXN_TBL_R is object
("TXN_ID" Number,
"USER_NAME" VARCHAR2(50 BYTE),
"TXN_DATE" DATE,
"TXN_AMOUNT" NUMBER)
and then create a table type that will store record of these types:
create or replace type TXN_TBL_TB as table of  TXN_TBL_R
The procedure that will perform the updates and or inserts is shown in the below snippet:-

create or replace procedure B_INSERT(p_in IN TXN_TBL_TB) as
cursor for_insert is select * from TABLE(p_in) rt where not exists(select tmp.TXN_ID from TXN_TBL tmp where rt.TXN_ID=tmp.TXN_ID);
cursor for_update is select * from TABLE(p_in) rt where exists(select tmp.TXN_ID from TXN_TBL tmp where rt.TXN_ID=tmp.TXN_ID);
temp_insert TXN_TBL_R;
temp_update TXN_TBL_R;
begin
for temp_update in for_update loop
update TXN_TBL tmp set tmp.USER_NAME= temp_update.USER_NAME,tmp.TXN_DATE=temp_update.TXN_DATE,tmp.TXN_AMOUNT=temp_update.TXN_AMOUNT where tmp.TXN_ID= temp_update.TXN_ID;
end loop;
for temp_insert in for_insert loop
insert into TXN_TBL values(temp_insert.TXN_ID, temp_insert.USER_NAME, temp_insert.TXN_DATE, temp_insert.TXN_AMOUNT);
end loop;
end;

Then you can basically call this program from the ADF application using struct type. The snippet is shown below:-
/**
*@param valueSet the set of bean values
*/
public void someMethod(Set valueSet){
Connection conn=null;
try {
conn = getDBTransaction().createStatement(1).getConnection();
StructDescriptor tblRecordStructType =
StructDescriptor.createDescriptor("TXN_TBL_R", conn);
Iterator it= valueSet.iterator();
Object txnArray[]=new Object[set.size()];
while(it.hasNext()){
SomeCustomBean detail=it.next();
STRUCT tempStruct=new STRUCT(tblRecordStructType,conn,new Object[]{detail.getTranxId(),detail.getUserName(),detail.getTxnDate(),detail.getTransAmount()});
txnArray[i]=tempStruct;
i=i+1;
}
//create array structure descriptor
ArrayDescriptor txnTableDesc=ArrayDescriptor.createDescriptor("TXN_TBL",conn);
//create an Array type with given structure definition
ARRAY txnTableArray=new ARRAY(txnTableDesc,conn,txnArray);
String callableProcedureStatement=" begin TMP_INSERT(?); end;" ;
OracleCallableStatement st=null;
st=(OracleCallableStatement)getDBTransaction().createCallableStatement(callableProcedureStatement, 0);
st.setARRAY(1, txnTableArray);
st.executeUpdate();
this.getDBtransaction().commit();
} catch (JboException e) {
this.getDBtransaction().rollBack();
throw new JboException(e.getMessage());

}
}


This is basically it. This will perform faster than normal ADF update/insert.