This section describes how to measure the performance of inserting rows using a ResultSet object.
Since ResultSet objects can also be used to insert rows, I wrote the following Java sample program
to measure the performance of inserting multiple rows using a ResultSet object:
/**
* SqlServerPerformanceResultSet.java
* Copyright (c) 2007 by Dr. Herong Yang. All rights reserved.
*/
import java.sql.*;
public class SqlServerPerformanceResultSet {
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();
// ResultSet to insert rows
Statement rs = con.createStatement(
ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_UPDATABLE);
ResultSet res = rs.executeQuery("SELECT * FROM Profile");
res.moveToInsertRow();
java.util.Random r = new java.util.Random();
for (int i = 0; i < count; i++) {
res.updateString("FirstName",
Integer.toHexString(r.nextInt(9999)));
res.updateString("LastName",
Integer.toHexString(r.nextInt(999999)));
res.insertRow();
}
rs.close();
// End the test
long t2 = System.currentTimeMillis();
System.out.println("ResultSet 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.
ResultSet insert 10000 rows with 6203 milliseconds
ResultSet is a little bit faster than regular statement, see the table below:
Statement # of inserts Time in ms. Comparison
PreparedStatement 10000 5625 100%
Regular Statement 10000 6750 120%
ResultSet 10000 6203 110%
