Re: PLSQL 4X faster than internal JAVA?

In article <d0bb6654.0107120738.6cb6f7ba_at_posting.google.com>, cmercer_at_vibrant-1.com says...
>
>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 ....
>

PLSQL and SQL are so tightly coupled, so tightly integrated -- it'll be hard to beat with any procedural languages whose basis is not SQL.

Between SQL and all procedural languages there is an impedance mismatch -- the datatypes in PLSQL for example are -- SQL datatypes. The datatypes in Java are -- NOT SQL datatypes. Right there, you have an impedance mismatch.

Also, since PLSQL was written pretty much to do -- SQL, it has micro optimizations you are not even aware of. Your java code for example:

> 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();
> }
> }

so 50,000 MORE soft parses then the PLSQL does! PLSQL will cache that open cursor (if if you say "close cursor_name", it'll remain OPEN in the hopes you reuse it). So, you artificially crippled your java routine by putting the prepareCall and close instead the loop. A good java program will prepare a statement ONCE per program execution -- never more. A PLSQL program using static SQL has that done for it.

Since PLSQL is written for SQL -- its interaction with SQL will necessarily be better, faster, easier then most anything else.

Even if you use N-Comped Java code in the database (compiled to object code) you will not see a huge speed up in this case -- Java just has to do more work to interact with SQL then PLSQL does.

PLSQL is, and most likely will continue to be, the appropriate choice for things "SQL".
>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;

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp