Jerzy is right. But there is also a very serious problem. Depending on how
often your garbage collector is executed you might run into a maximum
number of cursor exceeded error. For every CallableStatement an cursor is
opened. This will be closed at the point of descruction of this object.
So open once this callablestatment and reuse it!
Henning
In article <9jr397$asq$1_at_news.chatlink.com>, "Jerzy Tomasik"
<tomasik_at_datapacket.net> wrote:
> 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;
>
>
--
Henning Follmann
8 Jane Road
New Providence, NJ 07974
Tel.: +1 908 464 8972
H.Follmann_at_gmx.de
-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 80,000 Newsgroups - 16 Different Servers! =-----
Received on Fri Jul 27 2001 - 08:57:29 CDT
