Re: PLSQL 4X faster than internal JAVA?

I would try removing the loop invariant in the Java code. You are executing the constructor for CallableStatement 50,000 times. This makes it an unfair comparison, IMO.

Please post the results with the change.

Regards,
Jerzy

"Carl Mercer" <cmercer_at_vibrant-1.com> wrote in message news:d0bb6654.0107120738.6cb6f7ba_at_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 Fri Jul 27 2001 - 01:54:44 CDT