JDBC Tutorials - Herong's Tutorial Examples - v3.14, by Herong Yang
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:
/* DerbyPerformancePreparedStatement.java
* Copyright (c) HerongYang.com. All Rights Reserved.
*/
import java.util.*;
import java.sql.*;
import javax.sql.*;
import javax.naming.*;
public class DerbyPerformancePreparedStatement {
public static void main(String [] args) {
Connection con = null;
try {
Hashtable env = new Hashtable();
env.put(Context.INITIAL_CONTEXT_FACTORY,
"com.sun.jndi.fscontext.RefFSContextFactory");
env.put(Context.PROVIDER_URL, "file:/local/fscontext");
Context ctx = new InitialContext(env);
DataSource ds = (DataSource) ctx.lookup("DerbyTestDB");
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 (?, ?)");
Random r = new 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 7953 milliseconds
Table of Contents
JDBC (Java Database Connectivity) Introduction
Installing and Running Derby (Java DB)
Derby (Java DB) JDBC DataSource Objects
Derby (Java DB) - DML Statements
Derby (Java DB) - ResultSet Objects of Queries
►Derby (Java DB) - PreparedStatement
PreparedStatement with Parameters
PreparedStatement in Batch Mode
►Performance of Inserting Rows with a PreparedStatement
Performance of Inserting Rows with a Regular Statement
Performance of Inserting Rows with a ResultSet
Summary of JDBC Drivers and Database Servers