∟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:
/**
* SqlServerPerformancePreparedStatement.java
* Copyright (c) 2007 by Dr. Herong Yang. All rights reserved.
*/
import java.sql.*;
public class SqlServerPerformancePreparedStatement {
public static void main(String [] args) {
Connection con = null;
try {
com.microsoft.sqlserver.jdbc.SQLServerDataSource ds
= new com.microsoft.sqlserver.jdbc.SQLServerDataSource();
ds.setServerName("localhost");
ds.setPortNumber(1269);
ds.setDatabaseName("AdventureWorksLT");
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 (FirstName, LastName) VALUES (?, ?)");
java.util.Random r = new java.util.Random();
for (int i = 0; i < count; i++) {
ps.setString(1,Integer.toHexString(r.nextInt(9999)));
ps.setString(2,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 5625 milliseconds
