Upscaling your JDBC app using Oracle object type collection

Regarding oracle jdbc bulk array inserts, Greg Rahn wrote this week about the performance gains to be had by batching up calls to the database using the array interface.

As an addendum to his excellent points, please find attached a comparison with using an Oracle collection of an Oracle object type – forgive my Java. Using StructDescriptor, STRUCT, ArrayDescriptor and ARRAY structures is unsightly and unintuitive but they can deliver some further performance gains. If only we could wrap this approach up in some user-friendly layer then I reckon we could kick some of these iterative row-by-row ORM tools into touch.

First up, for the baseline, based On Greg’s example, this is what my batch size performance was like inserting 10000 rows into emp on my system:
jdbc-update-batching-performance.gif

And, using an Oracle collection of Oracle object types, uploading the 10000 rows in a single INSERT… TABLE … CAST statement it took 0.219 secondsJava class here.

Which compared very favourably.

Inline scripts:

create type to_emp as object
(EMPNO NUMBER(4)
,ENAME VARCHAR2(10)
,JOB VARCHAR2(9)
,MGR NUMBER(4)
,HIREDATE DATE
,SAL NUMBER(7,2)
,COMM NUMBER(7,2)
,DEPTNO NUMBER(2));
/

create type tt_emp as table of to_emp;
/
import java.sql.*;
import java.util.*;
import oracle.jdbc.*;
import oracle.jdbc.pool.OracleDataSource;
import oracle.sql.STRUCT;
import oracle.sql.StructDescriptor;
import oracle.sql.ArrayDescriptor;
import oracle.sql.ARRAY;


public class bulkInsert {

    public static void main(String[] args) {
        try {

            OracleDataSource ods = new OracleDataSource();
            ods.setURL("jdbc:oracle:oci8:@ora.noldb507");
            ods.setUser("scott");
            ods.setPassword("tiger");
            OracleConnection conn = (OracleConnection) ods.getConnection();
            conn.setAutoCommit(false);

            short seqnum = 0;
            String[] metric = new
                    String[OracleConnection.END_TO_END_STATE_INDEX_MAX];

            metric[OracleConnection.END_TO_END_ACTION_INDEX] = "insertEmp";
            metric[OracleConnection.END_TO_END_MODULE_INDEX] = "bulkInsert";
            metric[OracleConnection.END_TO_END_CLIENTID_INDEX] = "myClientId";
            conn.setEndToEndMetrics(metric, seqnum);

            DatabaseMetaData meta = conn.getMetaData();

            System.out.println(
                    "JDBC driver version is " + meta.getDriverVersion());

            Statement stmt = conn.createStatement();

            stmt.execute("alter session set sql_trace=true");
            stmt.execute("truncate table emp");

			int numberOfEmployees = Integer.parseInt(args[0]);

            STRUCT[] structEmployee = new STRUCT[numberOfEmployees];

            oracle.sql.StructDescriptor descEmployee = oracle.sql.StructDescriptor.createDescriptor("SCOTT.TO_EMP",conn);

            java.sql.Timestamp now = new java.sql.Timestamp( (new java.util.Date() ).getTime() );

            for (int i = 0; i < numberOfEmployees; i++) {

                Object[] empValues = {
                  (i), // EMPNO
                  ("Name" + i), // ENAME
                  ("Job"), // JOB
                  (i), // MGR
                  now , //now
                  (10000 + i), // SAL
                  (100 + i), // COMM
                  (10) // DEPTNO
                };

                structEmployee[i] = new oracle.sql.STRUCT(descEmployee, conn, empValues);
            }

            oracle.sql.ArrayDescriptor empArrayDescriptor = oracle.sql.ArrayDescriptor.createDescriptor("SCOTT.TT_EMP",conn);

            ARRAY empArray = new ARRAY(empArrayDescriptor,conn,structEmployee);

            OraclePreparedStatement psEmp = (OraclePreparedStatement) conn.prepareStatement(
             "insert /* insEmpBulk */ into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) select * from table (cast (? as tt_emp))");

            psEmp.setObject(1,empArray);

            long start1 = System.currentTimeMillis();

            // Set the batch size for each statment
            ((OraclePreparedStatement) psEmp).execute();

            conn.commit();
            psEmp.close();

            long elapsedTimeMillis1 = System.currentTimeMillis() - start1;
            // Get elapsed time in seconds
            float elapsedTimeSec1 = elapsedTimeMillis1 / 1000F;

            System.out.println("elapsed seconds: " + elapsedTimeSec1);

            conn.close();

        } catch (Exception e) {
            System.err.println("Got an exception! ");
            System.err.println(e.toString());
        }
    }
}
About these ads

8 Responses to Upscaling your JDBC app using Oracle object type collection

  1. Caustic Dave says:

    Thank you for the tip. I’m trying like mad to speed up an Oracle app at my workplace. I’m new to Oracle and appreciate you posting your results.

  2. Pingback: Three months since my last confession… « OraStory

  3. dombrooks says:

    Note that if I was doing this for real, I’d doubtless pass the array into a stored proc which did the insert…select rather than having the SQL in the java.

  4. Arindam Mukherjee says:

    In our web page, there are two blocks – transaction header and transaction details. In header block, there is one record but in detail block, there are multiple records.

    Now java needs to all those records (ONE for header and Multiple for Details) to oracle procedure and oracle procedure takes charge of data insertion with some calculation in oracle database.

    My question – how to establish handshake between oracle and java – how do you send multiple rows or result sets of detail block to oracle procedure.

  5. dombrooks says:

    Thanks for stopping by Arindam.

    You’ve a number of choices .

    My preference would be to use Oracle collections. To me, it makes the most sense from a transactional view and from the perspective of mapping your objects to your relational database.

    But it does mean using some ugly java code in terms of structdescriptors, etc.

    I would presume that you have some sort of “transaction” object (your definition of whatever a transaction object is) , made up of one transaction header and zero, one or more transaction details.

    And I would create the same structure in Oracle.

    For example (and I don’t know the specifics of your transaction header/detail), something like this:

    create or replace type to_transaction_detail as object
    (linenumber number
    ,product_id number
    ,description varchar2(20)
    ,quantity number);
    /

    create or replace type tt_transaction_detail as table of to_transaction_detail;
    /

    create or replace type to_transaction_header as object
    (transaction_id number
    ,customer_id number
    ,description varchar2(20)
    ,transaction_date date);
    /

    create or replace type to_transaction as object
    (transaction_header to_transaction_header
    ,transaction_details tt_transaction_detail);
    /

    And then you need to get dirty with the structs and struct descriptors so that you make one call to the database to save your transaction object.

    And that call can deal with any inserts or updates if they need to happen (or do nothing if the data is the same).

    Otherwise you’re probably looking at making one call to procedure to save each transaction detail passing in the transaction id for each detail line.

  6. Chris Adkin says:

    Regarding the comment about a nice encapsulation, if there is an ORM framework that does this it will most probably be Oracle TopLink or Eclipse Link, which is a JPA provider based on Toplink. Otherwise there is no reason why you cannot use raw JDBC + JPA or the ORM of your choice. I’m reliably informed that there are people who do this out there and make a good job of it.

    The cleanest way to incorporate this into your software is to place the JDBC within a data access object corresponding to the domain object, the thing that contains the business logic, a bog standard Java design pattern.

  7. Rakesh Chintha says:

    Thanks for the post !
    I had been working on this issue for over few hrs now and it just got resolved by applying this idea of yours ..

    It worked instantly for me !

    Thanks again
    RC

    • dombrooks says:

      No problem RC. Thanks for the feedback.

      From your comment on the other post, I take it that you solved an issue with the variable in lists using this approach?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 70 other followers

%d bloggers like this: