∟Performance of Inserting Rows with a PreparedStatement
This section describes how to measure the performance of inserting rows using a PreparedStatement object.
Running SQL statements using PreparedStatement objects is supposed to be faster than using regular Statement objects.
To test this, I wrote the following Java program to measure the performance of inserting rows using a PreparedStatement object into an empty table:
/**
* OraclePerformancePreparedStatement.java
* Copyright (c) 2007 by Dr. Herong Yang. All rights reserved.
*/
import java.sql.*;
public class OraclePerformancePreparedStatement {
public static void main(String [] args) {
Connection con = null;
try {
oracle.jdbc.pool.OracleDataSource ds
= new oracle.jdbc.pool.OracleDataSource();
ds.setDriverType("thin");
ds.setServerName("localhost");
ds.setPortNumber(1521);
ds.setDatabaseName("XE");
ds.setUser("Herong");
ds.setPassword("TopSecret");
con = ds.getConnection();
// Delete all rows from the table
Statement sta = con.createStatement();
sta.executeUpdate("DELETE FROM Profile");
// Start the test
int count = 10000;
long t1 = System.currentTimeMillis();
// PreparedStatement to insert rows
PreparedStatement ps = con.prepareStatement(
"INSERT INTO Profile (ID, FirstName, LastName)"
+ " VALUES (?, ?, ?)");
java.util.Random r = new java.util.Random();
for (int i = 0; i < count; i++) {
ps.setInt(1,i+1);
ps.setString(2,Integer.toHexString(r.nextInt(9999)));
ps.setString(3,Integer.toHexString(r.nextInt(999999)));
ps.executeUpdate();
}
ps.close();
// End the test
long t2 = System.currentTimeMillis();
System.out.println("PreparedStatement insert "+count
+" rows with "+(t2 -t1) +" milliseconds");
con.close();
} catch (Exception e) {
System.err.println("Exception: "+e.getMessage());
e.printStackTrace();
}
}
}
Here is the result on a Windows XP system with a 997MHz processor. You should compare this with the result the next tutorial.
PreparedStatement insert 10000 rows with 7843 milliseconds
