PLSQL 4X faster than internal JAVA?

From: Carl Mercer <cmercer_at_vibrant-1.com>
Date: Sat, 21 Jul 2001 21:33:46 GMT
Message-ID: <d0bb6654.0107120738.6cb6f7ba@posting.google.com>

I was under the impression that a Java stored procedure should run at roughly the same speed as PLSQL. This was due to the Oracle tuned JVM and compiling to native byte code and ....

Anyway, I have what I think are identical procedures in terms of functionality. One written in Java and one written in PLSQL. They each query the emp table and select ename, job. The 14 rows returned are assigned to string variables. Thats it. I do this in a loop 50,000 times. I get an average (5 runs) time from PLSQL of 39.0 seconds (just timed with select sysdate from dual). I get an average time from the Java stored procedure of 148.2 seconds. This seems off to me. I'm not sure whether to expect the PLSQL to be slower or the Java to be faster. Below are the procedures and how I called them. Any help or ideas why this is happening would be of great help.

P.S. I am doing similar things against Sybase 12.0 and get similar results.
Also, this is against 9.0.1 with little to no tuning done.

CREATE OR REPLACE PROCEDURE plsql_pli
AS

    CURSOR emp_tab IS SELECT ename, job FROM emp ORDER by ename;     i NUMBER;
    j NUMBER;
    ename VARCHAR2(100);
    job VARCHAR2(100);

    BEGIN

        i := 0;
        j := 0;
        LOOP
            FOR tab_rec IN emp_tab LOOP
                i := i + 1;
                ename := tab_rec.ename;
                job := tab_rec.job;
            END LOOP;
        j := j + 1;
        EXIT WHEN j = 50000;
        END LOOP;

    END;
/

import java.sql.*;
import java.io.*;

class procLoop
{

    public static void main (String [] args)
{

    try
{

        String driverVal = "oracle.jdbc.driver.OracleDriver";
        String connectStr = "jdbc:oracle:thin:@ivserv5:1521:TSTDB";
        String eName;
        String job;
        int loopCtr;
        String sql =
        "SELECT ename, job FROM emp ORDER BY ename";
        Connection conn;

        Class.forName(driverVal);
        conn = DriverManager.getConnection(connectStr, "cam",

"morgan");

        for (loopCtr = 0; loopCtr < 50000; loopCtr++)
        {
           CallableStatement pstmt = conn.prepareCall(sql);
            ResultSet rset = pstmt.executeQuery();
            while (rset.next())
            {
                eName = rset.getString(1);
                job = rset.getString(2);
            }
            pstmt.close();
        }

    }
    catch (SQLException e)
{

         System.err.println(e.getMessage());     }
    catch (Exception ee)
{

         System.err.println(ee.getMessage());     }
    }
}

sqlplus ....

CREATE OR REPLACE PROCEDURE PLI
    AS LANGUAGE JAVA
    NAME 'procLoopIn.procLoopIn()';
/

select to_char(sysdate, 'HH-MI-SS') from dual; call pli();
select to_char(sysdate, 'HH-MI-SS') from dual;

select to_char(sysdate, 'HH-MI-SS') from dual;
call plsql_pli();
select to_char(sysdate, 'HH-MI-SS') from dual;

Received on Sat Jul 21 2001 - 16:33:46 CDT