Re: fluctuating execution times

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 - 12:40:39 CST