Re: fluctuating execution times

Hmmm,
  this won't be germane to the discussion on the whys of fluctuating execution times,
  but a comment on your openConnection method.

  I would not rely on driver being the controlling flag for your connection validity during your applications complete session.

  You may run into the case of the connection going null or closing due to events beyond your (application's) control. In that scenario, your driver string would still hold information from an initial connect, but would prevent your application from reattaining the connection if it had gone south (think db crash).

  Instead use:

     if (dbCon == null || dbCon.isClosed()) {
         // get connection
     }

Cheers,
Cindy

Steffen Ramlow wrote:

> this is not true:
>
> 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();
> }
>
> it is done once per lifetime of the application
>
> "Jim Kennedy" <kennedy-family_at_attbi.com> wrote in message
> news:ue0_7.15404$Sf2.133740_at_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 - 17:11:38 CST