Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: fluctuating execution times

Re: fluctuating execution times

From: Steffen Ramlow <s.ramlow_at_gmx.de>
Date: Sun, 6 Jan 2002 16:05:59 +0100
Message-ID: <a19p5e$on7oi$1@ID-54600.news.dfncis.de>


sorry for the long post, but i cannot attach a file in this NG

package batchtest;

import java.sql.*;

public class MainClass {

  private boolean MSS = false;

// private String driverName = "com.inet.tds.TdsDriver"; // private String jdbcURL =
"jdbc:inetdae7a:msrvbp666:1433?database=tx_test";

// private String driverName = "com.ashna.jturbo.driver.Driver"; // private String jdbcURL = "jdbc:JTurbo://msrvbp666:1433/tx_test";

// private String driverName =
"com.microsoft.jdbc.sqlserver.SQLServerDriver"; // private String jdbcURL =
"jdbc:microsoft:sqlserver://msrvbp666:1433;DatabaseNameOPTIONAL=tx_test";

//  private String BATCH_START = "";
//  private String BATCH_END = " go";
//  private String STMT_SEP = " ";



// private String driverName = "com.inet.ora.OraDriver"; // private String jdbcURL = "jdbc:inetora:msrvbp666:1521:ORCL";

  private String driverName = "oracle.jdbc.driver.OracleDriver";   private String jdbcURL = "jdbc:oracle:thin:@msrvbp666:1521:ORCL";

  private String BATCH_START = "begin ";   private String BATCH_END = " end; ";
  private String STMT_SEP = "; ";

  private String userId = "Steffen";
  private String password = "ceus";

  private Connection dbCon = null;
  private int iterations = 1000;
  private int sp_iterations = iterations / 3;

  private String PREP_SQL_SP = "{ call BT1 (?,?,?) }";   private String PREP_SQL_SQL = "insert into BatchTest (c1, c2, c3) values (?, ?, ?)";
  //private String PREP_SQL_SQL2 = "select * from BatchTest where c1=? and c2=? and c3=?";

  private String V_C1 = "";
  private String V_C2 = "";
  private String V_C3 = "";

  private String driver = "";

  public static void main(String[] args) throws Exception {

    MainClass m = new MainClass();

    int testIts = 10;

    m.STMT_SEP = " ";

    for (int i = 0; i < testIts ; i++) {       m.runTest1();//unprep. stmts
}

    for (int i = 0; i < testIts ; i++) {       m.runTest2();//unprep. batch
}

    for (int i = 0; i < testIts ; i++) {       m.runTest4();//prep. stmts
}

    for (int i = 0; i < testIts ; i++) {       m.runTest5();//prep. stmts of sp calls
}

    for (int i = 0; i < testIts ; i++) {       m.runTest7();//prep batch
}

    for (int i = 0; i < testIts ; i++) {       m.runTest8();//prep batch of sp calls
}

    m.STMT_SEP = "; ";
    for (int i = 0; i < testIts ; i++) {
      m.runTest6();//unprep. sql batch of sp calls --

}

    for (int i = 0; i < testIts ; i++) {       m.runTest3();//unprep. sql batch --
}

  }

  private void openConnection() throws Exception {

      if (driver.equals("")) {
        Class.forName(driverName);
        this.dbCon = DriverManager.getConnection(jdbcURL, userId, password);

        driver=dbCon.getMetaData().getDriverName();
        System.out.println (driver);
        System.out.println ("batchsupport: " +
dbCon.getMetaData().supportsBatchUpdates());
      }

      prepareTest();

  }

  private void closeConnection() throws Exception {     //this.dbCon.close();

  }

  private void prepareTest() throws Exception {

    Statement s = this.dbCon.createStatement();

    s.execute("truncate table batchtest");     //s.execute("insert into BatchTest (c1, c2, c3) values ('0','0',0)");     //Thread.sleep(3000);

  }

  private void runTest1() throws Exception {

    openConnection();

    Statement s = this.dbCon.createStatement();

    long t1 = System.currentTimeMillis();

    for (int i = 0; i < iterations; i++) {       s.execute(createSQL(i).toString());
}

    long t2 = System.currentTimeMillis();

    closeConnection();

    long t3 = t2 - t1;
    System.out.println ("1) " + iterations + " unprepared stmts: " + createSQL(0) + ": " + t3 + " ms");

   }

  private void runTest2() throws Exception {

    openConnection();

    Statement s = this.dbCon.createStatement();

    long t1 = System.currentTimeMillis();

    for (int i = 0; i < iterations; i++) {       s.addBatch(createSQL(i).toString());
}

    execBatch(s);

    long t2 = System.currentTimeMillis();

    closeConnection();

    long t3 = t2 - t1;
    System.out.println ("2) single unprepared jdbc batch with " + iterations + " stmts: " + createSQL(0) + ": " + t3 + " ms");

  }

  private void runTest3() throws Exception {

    openConnection();

    long t1 = System.currentTimeMillis();

    Statement s = this.dbCon.createStatement();     StringBuffer sql = new StringBuffer();

    sql.append(BATCH_START);
    for (int i = 0; i < iterations; i++) {       sql.append(createSQL(i).toString());
}

    sql.append(BATCH_END);

    s.execute(sql.toString());

    long t2 = System.currentTimeMillis();

    closeConnection();

    long t3 = t2 - t1;
    System.out.println ("3) single unprepared sql batch with " + iterations + " stmts: " + createSQL(0) + ": " + t3 + " ms");

  }

  private void runTest4() throws Exception {

    openConnection();

    PreparedStatement s = this.dbCon.prepareStatement(PREP_SQL_SQL);

    long t1 = System.currentTimeMillis();

    int i = 0;

    for (i = 0; i < iterations; i++) {

      String s1 = i + V_C1;
      String s2 = i + V_C2;
      String s3 = i + V_C3;

      s.setString(1, s1);
      s.setString(2, s2);
      s.setString(3, s3);

      s.execute();

}

    long t2 = System.currentTimeMillis();

    closeConnection();

    long t3 = t2 - t1;
    System.out.println ("4) " + iterations + " prepared stmts: " + PREP_SQL_SQL + ": " + t3 + " ms");
  }

  private void runTest5() throws Exception {

    openConnection();

    CallableStatement s = this.dbCon.prepareCall(PREP_SQL_SP);     //PreparedStatement s = this.dbCon.prepareStatement(PREP_SQL_SP);

    long t1 = System.currentTimeMillis();

    for (int i = 0; i < sp_iterations; i++) {

      String s1 = i + V_C1;
      String s2 = i + V_C2;
      String s3 = i + V_C3;

      s.setString(1, s1);
      s.setString(2, s2);
      s.setString(3, s3);

      s.execute();

}

    long t2 = System.currentTimeMillis();

    closeConnection();

    long t3 = t2 - t1;
    System.out.println ("5) " +sp_iterations + " prepared stored proc calls: " + PREP_SQL_SP + ": " + t3 + " ms");

  }

  private void runTest6() throws Exception {

    openConnection();

    long t1 = System.currentTimeMillis();

    Statement s = this.dbCon.createStatement();     StringBuffer sql = new StringBuffer();

    sql.append(BATCH_START);
    for (int i = 0; i < sp_iterations; i++) {       sql.append(createSPSQL(i).toString());
}

    sql.append(BATCH_END);

    //System.out.println (sql.toString());

    s.execute(sql.toString());

    long t2 = System.currentTimeMillis();

    closeConnection();

    long t3 = t2 - t1;
    System.out.println ("6) single unprepared sql batch with " + sp_iterations + " stored proc calls: " + createSPSQL(0) + ": " + t3 + " ms");

  }

   private void runTest7() throws Exception {

    openConnection();

    PreparedStatement s = this.dbCon.prepareStatement(PREP_SQL_SQL);

    long t1 = System.currentTimeMillis();

    for (int i = 0; i < iterations; i++) {

      String s1 = i + V_C1;
      String s2 = i + V_C2;
      String s3 = i + V_C3;

      s.setString(1, s1);
      s.setString(2, s2);
      s.setString(3, s3);

      s.addBatch();

}

    int r[];

    execBatch(s);

    long t2 = System.currentTimeMillis();

    closeConnection();

    long t3 = t2 - t1;
    System.out.println ("7) single prepared jdbc batch with " + iterations + " stmts: " + PREP_SQL_SQL + ": " + t3 + " ms");   }

  private void runTest8() throws Exception {

    openConnection();

    CallableStatement s = this.dbCon.prepareCall(PREP_SQL_SP);

    long t1 = System.currentTimeMillis();

    for (int i = 0; i < sp_iterations; i++) {

      String s1 = i + V_C1;
      String s2 = i + V_C2;
      String s3 = i + V_C3;

      s.setString(1, s1);
      s.setString(2, s2);
      s.setString(3, s3);

      s.addBatch();

}

    execBatch(s);

    long t2 = System.currentTimeMillis();

    closeConnection();

    long t3 = t2 - t1;
    System.out.println ("8) single prepared jdbc batch with " + sp_iterations + " stored proc calls: " + PREP_SQL_SP + ": " + t3 + " ms");

  }

//helpers

  private void execBatch(Statement s) throws SQLException {

    int r[];

    try{
      r = s.executeBatch();
}

    catch (BatchUpdateException e) {

      r = e.getUpdateCounts();
      System.out.println (e.getMessage());

}

    long t2 = System.currentTimeMillis();

    //System.out.println (r.length);

    for (int i = 0; i < r.length; i++) {

      //System.out.println (r[i] + " @ " + i);
      if (r[i] < 0 && r[i] != -2) {
        System.out.println ("buuuhh: " + r[i] + " @ " + i);
      }

}

  }

  private StringBuffer createSQL(int i) {     StringBuffer s = new StringBuffer(3);

    s.append("insert into BatchTest (c1, c2, c3) values (");

    s.append("'");
    s.append(i + V_C1);
    s.append("',");

    s.append("'");

    s.append(i + V_C2);
    s.append("',");
    s.append(i + V_C3);
    s.append(")");
    s.append(STMT_SEP);

    //System.out.println (s.toString());

    return s;

  }

  private StringBuffer createSPSQL(int i) {     StringBuffer s = new StringBuffer(3);

    if (MSS == true) {

    s.append("exec BT1 ");

    s.append("'");
    s.append(i + V_C1);
    s.append("',");

    s.append("'");

    s.append(i + V_C2);
    s.append("',");

    s.append(i + V_C3);
    s.append(STMT_SEP);

}

    else {
      s.append("BT1(");

      s.append("'");
      s.append(i + V_C1);
      s.append("',");

      s.append("'");
      s.append(i + V_C2);
      s.append("',");

      s.append(i + V_C3);
      s.append(")");

      s.append(STMT_SEP);

}

    return s;

  }
}//eoc

"AV" <avek_nospam_at_videotron.ca> wrote in message news:fpHZ7.24611$CB1.922013_at_wagner.videotron.net... > Hello Steffen,

> If possible, can you describe (code fragment?) > every set and just average time results? Received on Sun Jan 06 2002 - 09:05:59 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US