In the application I am working on, there is the need to speed up the insert of a variable (but large) number of rows to a table. The inserts are simple, and right now we are using ordinary Statement objects. We thought we can speed it up by using PreparedStatement or CallableStatement to stored procedure. Our tests show that using PreparedStatement is slower than using Statement, and that using CallableStatement is the slowest of the three? Our application uses transaction service provided by COM+ (MSDTC), JDBC connection through MS ODBC for Oracle (2.573) and Oracle 8i (8.1.7).

Is there any good explanation for this?

Also, to determine if the MS ODBC driver was part of the problem I wrote some test app using the Oracle thin JDBC driver:

XXXXX

Class.forName("oracle.jdbc.driver.OracleDriver");
conn = java.sql.DriverManager.getConnection("jdbcracle: thin:....@......");
Statement del = conn.createStatement();
del.executeUpdate("delete from test_jdbc");

conn.setAutoCommit(false);
EventLog.log("Connection established!");

et.start();
Statement stmt = conn.createStatement();
int i = 0;
for(i = 0; i < 5000; i++){
String sql = "insert into test_jdbc values (" + i + "," + "'Peter', 'Some description goes here'," +
"1.2, Date '" + dd.toString() + "', 78458," +
i + "," + i + "," + i + "," + i + "," + i + ")";
stmt.executeUpdate(sql);
}
conn.commit();
et.stop();
et.logElapseTime(null, "Statement: ");

del.executeUpdate("delete from test_jdbc");
String sql = "insert into test_jdbc values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
et.start();
PreparedStatement pstmt = conn.prepareStatement(sql);
for(i = 0; i < 5000; i++){
pstmt.setInt(1, i);
pstmt.setString(2, "Peter");
pstmt.setString(3, "Some description goes here");
pstmt.setDouble(4, 1.2);
pstmt.setDate(5, dd);
pstmt.setLong(6, 78458);
pstmt.setInt(7, i);
pstmt.setInt(8, i);
pstmt.setInt(9, i);
pstmt.setInt(10, i);
pstmt.setInt(11, i);
pstmt.executeUpdate();
}
conn.commit();
et.stop();
et.logElapseTime(null, "PreparedStatement: ");
conn.close();

XXXX

I found that using Statement is about as fast as using PreparedStatement for 5000 inserts. Is there something wrong with the way I am using them or with the setup?

I appreciate any help.

Thanks.

-kcmyl

I don't think you should expect improved performance of PreparedStatements in your case. Improvement comes from the fact that the SQL optimizer would analyze the PreparedStatement only once and then re-use the execution plan for all subsequent executions of that statement. However, in case of simple insert statements the optimizer overhead is negligible - there's nothing to optimize.

Among the most common ways to increase performance of inserts are:
- reduce the number of indexes on the table
- disable triggers and constraints
- optimize free space management (may be PCTFREE is too high?)
- optimize space allocation (extent size and number, etc).

Hope this helps,

Nick