Results 1 to 4 of 4
  1. #1
    Join Date
    May 2002
    Posts
    5

    Unanswered: oracle Java Stored Procedure perfomance problem

    Hi!,

    Subject: oracle Java Stored Procedure, Resultset class (In Insensitive and Updatable mode) Performance problem

    Problem:

    Transaction time is longer because Resultset cursor takes longer time to update fields value many time in loop and then finally fetch to DB.
    The scrollable cursor is jumping forward, backward and nth position of record in resultset.

    If resultset contains 3000 records, cursor is jumping more than 50,000 times backward and forward.

    We are using
    1.Oracle 9i Release (9.0.1) Server from Oracle
    2. Windows 2000 server

    1. Is there any way or option to reduce transaction time from 90 sec to 6 sec for this particular example?

    2. Please let me know any other options.

    4.can any application server available in market give me such performance if I will shift to them? Presently, I am using JRun.


    Any help or suggestion will be appreciated.

    I tried also this

    1. Using VBA and MS access as database:

    I build the same logic in VBA and executed that program. Total execution time of code is 6 second.
    I am unable to find the solution why same program is consuming 90 second using oracle java stored procedure.



    Thanking you

    Regards

    Raj


    Source code is attached below.

    Code description:
    1. Select the fields order by ‘Date1’ field.
    3.If the Rest field value is less or equal to BP field value, update the Factor field by value 1. Move to next rows till when this condition is reached.
    4.When Rest<= BP happens, save date2 field value in variable cdt.
    5.jump to the row where date1 field value is greater or equal to this saved date2 and update the rest field value by adding BG field value to Rest field value till last row.
    6.Now Jump back to the row where you have started updating rest field value.
    7.Start checking again the Rest field value is less or equal to BP field value. Move to next rows till when this condition is reached.
    8.Start repeating the procedure from point 2 to 5 till the last row.


    Resultset ‘Rest’ field value are updated many time according to
    some condition and finally fetched to database.



    Sampletable before executing the Java_stored procedure:

    ArtikelNummer Date1 Menge BG BP Rest Factor Date2
    10080 8/2/1999 24 10 8 20 0 8/5/1999
    10080 8/3/1999 12 10 8 8 0 8/6/1999
    10080 8/4/1999 6 10 8 2 0 8/7/1999
    10080 8/4/1999 24 10 8 -22 0 8/7/1999
    10080 8/5/1999 6 10 8 -28 0 8/8/1999
    10080 8/6/1999 6 10 8 -34 0 8/9/1999
    10080 8/7/1999 6 10 8 -40 0 8/10/1999
    10080 8/8/1999 12 10 8 -52 0 8/11/1999
    10080 8/9/1999 6 10 8 -58 0 8/12/1999
    10080 8/10/1999 6 10 8 -64 0 8/13/1999

    Analysis:
    àUsing Oracle Java Stored procedure:


    1.Transaction time to fetch 3359 rows into resultset rs = 30 millisecond.
    3.Total Transaction time to execute the code = 92 Second.

    Important:
    This transaction time depends how many times ‘Factor’ field is updated by value 1. If It happens more, Rest field updating loop will occur more that consume time.

    AS I am getting 3359 records in Resultset in 20 millisecond and refetching time is also 4 sec approx.. So rest time (88 second) has taken updating the value in Resultset.


    Sampletable after executing the Java stored procedure:

    ArtikelNummer Date1 Menge BG BP Rest Factor Date2
    10080 8/2/1999 24 10 8 20 0 8/5/1999
    10080 8/3/1999 12 10 8 8 1 8/6/1999
    10080 8/4/1999 6 10 8 2 0 8/7/1999
    10080 8/4/1999 24 10 8 -22 0 8/7/1999
    10080 8/5/1999 6 10 8 -28 0 8/8/1999
    10080 8/6/1999 6 10 8 -24 1 8/9/1999
    10080 8/7/1999 6 10 8 -30 0 8/10/1999
    10080 8/8/1999 12 10 8 -42 0 8/11/1999
    10080 8/9/1999 6 10 8 -38 1 8/12/1999
    10080 8/10/1999 6 10 8 -44 0 8/13/1999




    Source code:

    import java.sql.*;
    import java.util.*;
    import java.io.*;
    import oracle.jdbc.*;
    public class trail {
    public static void trailtwo(int cc) throws SQLException
    {
    Connection connection=null;
    PreparedStatement statement4=null;
    java.util.Date adt;
    java.util.Date m;
    double bd;
    double h;
    double g;
    double l;
    double n;
    double o;
    java.util.Date cdt;
    int fs=0;

    try {


    connection =DriverManager.getConnection("jdbc:default:connect ion:");
    connection.setAutoCommit(false);

    long now8 = System.currentTimeMillis();
    System.out.println("Start time for forstmt4"+now8);

    statement4 = connection.prepareStatement("SELECT rowid,/*+INDEX(T indexdatum)+*/ artikelnummer,datum,restbestand,Bestellungausgelös t,bestellpunkt,bestellgrösse,WBZDatum,artikelseque nce from auftragdaten_level_sub2 T where datum > '10-JAN-1999'",ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet .CONCUR_UPDATABLE);

    ResultSet rs4 = statement4.executeQuery();

    int y=rs4.getFetchSize();
    System.out.print(y);
    rs4.setFetchSize(3360);
    long later9 = System.currentTimeMillis();
    System.out.println("End time forstmt4 "+later9);
    System.out.println("The time taken for retrieving records of forstmt4 is "+ ((later9-now8)));

    while (rs4.next())
    {
    adt= rs4.getDate("Datum");
    bd= rs4.getDouble("bestellgrösse");
    h =rs4.getDouble("restbestand");
    g =rs4.getDouble("bestellpunkt");
    l = rs4.getDouble("Bestellungausgelöst");

    if( h <= g)
    {
    cdt= rs4.getDate("WBZDatum");
    fs= rs4.getInt("artikelsequence");
    fs=fs+1;
    //System.out.print(cdt);
    rs4.updateInt(5, 1);
    rs4.updateRow();
    if (rs4.isLast())
    {
    break;
    }
    while (rs4.next())
    {
    m= rs4.getDate("Datum");
    //System.out.print(m);
    if((m.after(cdt))|| (m.equals(cdt)))
    {
    n =rs4.getDouble("restbestand");
    o =n+bd;
    rs4.updateDouble(4, o);
    rs4.updateRow();
    }
    }

    rs4.first();
    while (rs4.next())
    {
    m= rs4.getDate("Datum");
    if(m.after(cdt))
    {
    fs= rs4.getInt("artikelsequence"); rs4.last();
    }
    }
    rs4.absolute(fs-1);
    }
    }

    rs4.close();
    statement4.close();
    connection.commit();
    long later8 = System.currentTimeMillis();
    System.out.println("End time forstmt4 "+later8);
    System.out.println("The time taken for retrieving records of forstmt4 is "+ ((later8-now8)));

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

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: oracle Java Stored Procedure perfomance problem

    Why would you write this pure data update process in Java? Bound to be slow!

    Here is a pure PL/SQL version of your process - neater, simpler, faster!
    Code:
    declare
      v_date date default to_date('01-jan-1998','dd-mon-yyyy'); 
      v_continue boolean := TRUE;
    begin
      while v_continue loop
        v_continue := FALSE;  -- Stop process once no more start rows found
        for r in (select t.*, t.ROWID rid
                  from   t
                  where  date1 >= v_date
                  and    rest <= bp
                  order by date1
                  for update of factor
                 )
        loop
          v_continue := TRUE;
          update t
          set    factor = 1
          where  t.ROWID = r.rid;
          update t
          set    rest = rest+bg
          where  date1 >= r.date2;
          v_date := r.date2;
          exit;
        end loop;
      end loop;
    end;
    /
    I ran it on your 10 row sample and got teh same results you get.

    It took 18 seconds to run on 3000 rows that I created as follows:

    Code:
    begin
      for i in 1..300 loop
        insert into t 
          select ARTIKELNUMMER, DATE1+10*i, menge, bg, bp, rest, factor, date2+i*10
          from t_save;
       end loop;
    end;
    /
    (t_save is a copy of your test data).

    I had an index on date1.

    PL/SQL rules!

  3. #3
    Join Date
    May 2002
    Posts
    5

    Re: oracle Java Stored Procedure perfomance problem

    As I know, PL/SQL does not support 100% scrollable cursor. means some of move(), previous, next(), absolute() method are not supported. as i know cursor can go forward but not backward or backword absolute position..

    Am i right? otherwise i will check it again.. that is why at that time, i decided to jump to java procedure. may be, i had wrong information source. oracle claims, PL/SQL provide scrollable cursor but it was not 100% true as per my source of information. I will check that again..

    Thanks for reply

    Regards

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: oracle Java Stored Procedure perfomance problem

    Originally posted by kumarmalhotra
    As I know, PL/SQL does not support 100% scrollable cursor. means some of move(), previous, next(), absolute() method are not supported. as i know cursor can go forward but not backward or backword absolute position..

    Am i right? otherwise i will check it again.. that is why at that time, i decided to jump to java procedure. may be, i had wrong information source. oracle claims, PL/SQL provide scrollable cursor but it was not 100% true as per my source of information. I will check that again..

    Thanks for reply

    Regards
    You are right about PL/SQL cursors, they only move forward.

    However, the solution I gave gets round that limitation by re-
    opening the cursor each time you want to go back. Actually,
    I am not sure it is a limitation, because this way I get to
    use the power of indexes and set-based updates!

    Good luck!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •