Java Stored Procedures – Performance Test

Are Java Stored Procedures really faster than standalone Java Programs?

Let’s make the test!

Our test program consists of the following simple steps:

  1. Get a database connection
  2. Drop and create the test table “BASIC_LOB_TABLE”
  3. Insert two rows into “BASIC_LOB_TABLE”
  4. Select and process all the rows from “BASIC_LOB_TABLE”
  5. Read the BLOB and CLOB fields and get their size
  6. Truncate the BLOB and CLOB fields and get their size

1. Source code – equal for standalone and stored procedure! :

/* To create a standalone program, remove the "CREATE OR REPLACE..." line !!! */
CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "TrimLob" AS
import java.sql.Blob;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class TrimLob
{
    public static void main (String args [])
    throws SQLException
    {
        Connection conn;

        /* Are we in the database or outside? */
        if (System.getProperty("oracle.jserver.version") != null)
        {
            /* in the database, use the default connection */
            conn = DriverManager.getConnection("jdbc:default:connection:");
        }
        else
        {
            /* outside, connect to the database */
            DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
            conn = DriverManager.getConnection("jdbc:oracle:thin:@mydb:1521:MYSID", "MY_USER","mypassword");
        }

        long t0,t1;

        /* Turn auto commit off (Required when using SELECT FOR UPDATE) */
        conn.setAutoCommit(false);

        // Create a Statement
        Statement stmt = conn.createStatement ();

        // Drop the test table to ensure equal conditions:
        try {
            stmt.execute("drop table basic_lob_table");
        }
        catch (SQLException pE) {
            /* Ignore error "ORA-00942: table or view does not exist", throw all others: */
            if (pE.getErrorCode() != 942) {
                throw(pE);
            }
        }

        //Create the test table:
        stmt.execute("create table basic_lob_table (x varchar2 (30), b blob, c clob)");


        t0 = System.currentTimeMillis();

        // Insert two rows in the test table:
        stmt.execute ("insert into basic_lob_table values ('first', " +
            "'010101010101010101010101010101', " +
            „'one.two.three.four.five.six.seven')");
        stmt.execute ("insert into basic_lob_table values ('second', " +
            "'0202020202020202020202020202020202020202', " +
            "'two.three.four.five.six.seven.eight.nine.ten')");

        // Retreive LOBs and update contents (trim):
        ResultSet rset = stmt.executeQuery("select * from basic_lob_table for update");
        while (rset.next ())
        {
            // Get the lobs
            Blob blob = (Blob) rset.getObject (2);
            Clob clob = (Clob) rset.getObject (3);

            // Truncate the lobs
            blob.truncate (6);
            clob.truncate (6);
        }
        rset.close ();
        conn.commit();
        stmt.close ();
        conn.close ();

        // Print runtime:
        t1 = System.currentTimeMillis();
        System.out.println ((int)(t1-t0) + " ms");
    }
}
/

2. PL/SQL Wrapper:

CREATE OR REPLACE PROCEDURE TrimLobSp AS
language JAVA NAME 'TrimLob.main(java.lang.String[])';
/ 

3. Test:

SET serveroutput ON
CALL dbms_java.set_output(50000);
CALL TrimLobSp();

Runtime comparison – DML only (steps 3 to 6)

Run Standalone JServer faster

1

80 ms

10 ms

8 times

2

94 ms

8 ms

12 times

3

93 ms

8 ms

12 times

4

93 ms

8 ms

12 times

5

93 ms

7 ms

13 times

We did not measure the time to connect to the database, to drop and create the table or anything else. Only the pure DML times (insert, select, update) are measured.

And here is the answer: on our systems Java Stored Procedures are 12 times faster!

Now it’s your turn to measure your systems. Don’t be surprised.

Advertisements