Performance of Inserting Rows with a PreparedStatement

<< MySQL - PreparedStatement

<< JDBC Tutorials - Herong's Tutorial Notes

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:

/**
 * MySqlPerformancePreparedStatement.java
 * Copyright (c) 2007 by Dr. Herong Yang. All rights reserved.
 */
import java.util.*;
import java.sql.*;
import javax.sql.*;
public class MySqlPerformancePreparedStatement {
  public static void main(String [] args) {
    Connection con = null;
    try {
      com.mysql.jdbc.jdbc2.optional.MysqlDataSource ds 
        = new com.mysql.jdbc.jdbc2.optional.MysqlDataSource();
      ds.setServerName("localhost");
      ds.setPortNumber(3306);
      ds.setDatabaseName("HerongDB");
      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 (?, ?)");
      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 1281 milliseconds

Sections in This Chapter

PreparedStatement Overview

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