Speed of Java versus PL/SQL 1

[Custom24]

Hello
I have an stored procedure to write and I prefer Java personally, but thought that PL/SQL might be a better choice because of performance. I'm not an expert but doesn't it essentially run in the Oracle process as opposed to Java, which runs in it's own JVM?

I was surprised when I did a performance test and found that Java was twice as fast. The test is simply to read 1 million values from a table and return the sum (using a loop). Both are implemented as stored procedures. PL/SQL takes 20 seconds, and Java 12 seconds. It seems pretty reproducible. Nothing else goes on in the code - I have included it at the bottom of the post for reference.

Is this to be expected?

Thanks for any input.
Mark

CREATE OR REPLACE procedure TestSpeedPLSQL is
cursor LoopCursor is select t.INT_VALUE from test_speed t;
lRunningTotal number;
lThisValue number;
startTime date;
begin
startTime := sysdate;
lRunningTotal := 0;
open LoopCursor;
loop
fetch LoopCursor into lThisValue;
exit when LoopCursor%NotFound;
lRunningTotal := lRunningTotal + lThisValue;
end loop;
close LoopCursor;
dbms_output.put_line(lRunningTotal);
dbms_output.put_line((sysdate - startTime) * 86400);
end;
/

import java.sql.*;
import java.io.*;
import oracle.jdbc.driver.*;
public class TestOraJ2
{
public static double Total(int iValue)
{
//loop thru and sum values from test table
double iThisValue;
double iRunningTotal = 0;
System.out.println("This is from Java");
Connection conn = null;
try{
conn = new OracleDriver().defaultConnection();
String SQL = "Select Int_Value from Test_Speed";
Statement s = conn.createStatement();
ResultSet r = s.executeQuery(SQL);
while (r.next())
{
  iThisValue = r.getDouble(1);
  iRunningTotal += iThisValue;
}
r.close();
s.close();
}
catch (SQLException e)
{
  e.printStackTrace(System.err);
  return -1;
}
catch (Exception e)
{
  e.printStackTrace(System.err);
  return -2;
}
finally
{
try{conn.close();} catch(Exception e) {}
}
return iRunningTotal;
}
}

Mark

 

[sem]

Try to rewrite your PL/SQL version to utilize BULK COLLECT clause.
In most cases db-related operations are faster in pl/sql, while calculations - in java. In any ase for your specific case select sum() is the fastest alterative

Regards, Dima

http://seminihin.narod.ru

 

[Custom24]

Thanks Sem
The code using bulk collect takes just 3 seconds to run. I wonder if there is an equivalent for Java.

And I assume you knew I was purposefully avoiding select sum() because this was a test of performance for the kind of application I am writing.

Mark

CREATE OR REPLACE procedure TestSpeedPLSQLBC is
-- Version using Bulk Collect
Type num_tab is table of number;
value_nums num_tab;
cursor LoopCursor is select t.INT_VALUE from test_speed t;
lRunningTotal number;
lThisValue number;
lCounter number;
startTime date;
begin
startTime := sysdate;
lRunningTotal := 0;
open LoopCursor;
fetch LoopCursor Bulk Collect into value_nums;
close LoopCursor;
for lCounter in value_nums.first..value_nums.last
loop
lThisValue := value_nums(lCounter);
lRunningTotal := lRunningTotal + lThisValue;
end loop;
dbms_output.put_line(lRunningTotal);
dbms_output.put_line((sysdate - startTime) * 86400);
end;
/

Mark

 

[sem]

Oracle JDBC driver allows bulk operations but I'm not too experienced with Java stored procedures.

Regards, Dima

http://seminihin.narod.ru

 

[Custom24]

Thanks. I've looked into it, and there is kinda an equivalent, which is to set the fetch size of the statement. By default, this is 0, which means it retrieves one row at a time. However, if you alter my Java code to

...
Statement s = conn.createStatement();
[b]s.setFetchSize(1000);[/b]
...

Then, it improves the peformance, but not by the same dramatic factor as PL/SQL. (Depends on the fetch size you set, but seems to approx halve the execution time).

The two are coming down to approx the same kind of execution times, and as you've pointed out, there are other factors to consider in the choice.

But this has been a useful exercise for me. Thanks again for your help.

Mark

Mark