Performance of Inserting Rows with a Regular Statement

<< MySQL - PreparedStatement

<< JDBC Tutorials - Herong's Tutorial Notes

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:

/**
 * MySqlPerformanceRegularStatement.java
 * Copyright (c) 2007 by Dr. Herong Yang. All rights reserved.
 */
import java.util.*;
import java.sql.*;
import javax.sql.*;
public class MySqlPerformanceRegularStatement {
  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();

// Regular Statement to insert rows
      Statement rs = con.createStatement();
      Random r = new 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 1297 milliseconds

Comparing with PreparedStatement, regular Statement performs identically as PreparedStatement, see the table below:

Statement           # of inserts   Time in ms.   Comparison
PreparedStatement   10000           1281         100%
Regular Statement   10000           1297         101%

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