Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: fluctuating execution times
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 --
}
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_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_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