∟Performance of Inserting Rows with a Regular Statement
This section describes how to measure the performance of inserting rows using a Statement object.
To compare the performance result from the previous result with regular Statement objects.
I wrote the following Java program to measure the performance of inserting rows using a regular Statement object into an empty table:
/**
* SqlServerPerformanceRegularStatement.java
* Copyright (c) 2007 by Dr. Herong Yang. All rights reserved.
*/
import java.sql.*;
public class SqlServerPerformanceRegularStatement {
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();
// Regular Statement to insert rows
Statement rs = con.createStatement();
java.util.Random r = new java.util.Random();
for (int i = 0; i < count; i++) {
rs.executeUpdate("INSERT INTO Profile (FirstName, LastName)"
+" VALUES ('"+Integer.toHexString(r.nextInt(9999))
+"', '"+Integer.toHexString(r.nextInt(999999))+"')");
}
rs.close();
// End the test
long t2 = System.currentTimeMillis();
System.out.println("Regular Statement 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 from other performance tutorials.
Regular Statement insert 10000 rows with 6750 milliseconds
Comparing with PreparedStatement, regular Statement performs much slower than PreparedStatement, see the table below:
Statement # of inserts Time in ms. Comparison
PreparedStatement 10000 5625 100%
Regular Statement 10000 6750 120%
