Re: fluctuating execution times

From: Jim Kennedy <kennedy-family_at_attbi.com>
Date: Sun, 06 Jan 2002 17:57:46 GMT
Message-ID: <ue0_7.15404$Sf2.133740@rwcrnsc52>

The first thing I notice is that for each test you open and close a connection to the database. Bad practice. Open the connection once and keep it open.
for example:

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

       m.runTest1();//unprep. stmts
     }

and runTest1 is

   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");

    }

In the main body open the connection once and at the end close the connection.
Jim

"Steffen Ramlow" <s.ramlow_at_gmx.de> wrote in message news:a19p5e$on7oi$1_at_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 - 11:57:46 CST