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:

/* OraclePerformanceRegularStatement.java
 * Copyright (c) HerongYang.com. All Rights Reserved.
 */
import java.sql.*;
public class OraclePerformanceRegularStatement {
  public static void main(String [] args) {
    Connection con = null;
    try {
      oracle.jdbc.pool.OracleDataSource ds
        = new oracle.jdbc.pool.OracleDataSource();
      ds.setDriverType("thin");
      ds.setServerName("localhost");
      ds.setPortNumber(1521);
      ds.setDatabaseName("XE");
      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 (ID, FirstName, LastName) VALUES ("
          +(i+1)+", '"+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) {
      e.printStackTrace();
    }
  }
}

Here is the test results on my new Mac computer:

Operationg System: macOS 10
CPU speed:  2.6 GHz
JVM version: 15
Oracle Server: 21.3
JDBC driver: ojdbc11.jar

Statement           # of inserts   Time in ms.   Comparison
PreparedStatement   10000           9962         100%
Regular Statement   10000          19220         193%

Here is the test results on my new Windows computer:

Operationg System: Windows 10
CPU speed: 3.6 GHz
JVM version: 17.0.1
Oracle Server: 21.3
JDBC driver: ojdbc11.jar

Statement           # of inserts   Time in ms.   Comparison
PreparedStatement   10000          14281         100%
Regular Statement   10000          29648         208%

Test results on my older computer:

Operationg System: Windows 7
CPU speed: 2.5 GHz
JVM version: 1.8.0_45
Oracle Server: 11.2
JDBC driver: ojdbc6.jar

Statement           # of inserts   Time in ms.   Comparison
PreparedStatement   10000          22574         100%
Regular Statement   10000          63897         279%

Test results on my very old computer:

Operationg System: Windows XP
CPU speed: 997 MHz
JVM version: 1.6
Oracle Server: 10.2
JDBC driver: ojdbc14.jar

Statement           # of inserts   Time in ms.   Comparison
Regular Statement   10000          13609

Table of Contents

 About This Book

 JDBC (Java Database Connectivity) Introduction

 JDK (Java SE) Installation

 Oracle Express Edition Installation on Windows

 Oracle JDBC Drivers

 Oracle - Reference Implementation of JdbcRowSet

Oracle - PreparedStatement

 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

 Oracle - JBDC CallableStatement

 Oracle CLOB (Character Large Object) - TEXT

 Oracle BLOB (Binary Large Object) - BLOB

 Archived Tutorials

 References

 Full Version in PDF/EPUB