I therefore decided to implement a PreparedStatement version of the INSERT.
Before changing the call, however, I decide to test the difference in the performances.
I compared 3 versions of the database call.
The first was the old one, where statement.execute (sql) was used.
The second method used a PreparedStatement and the PreparedStatement.execute() call.
The third version used the same PreparedStatement, but using the bash version.
I tested it inserting, for each method, 1000 rows. The results are the follows:
statement.execute (sql) * 1000: 837 millisec
PreparedStatement.execute() * 1000: 625 millisec
PreparedStatement.addBatch() * 1000: 186 millisec
The performance gain is quite impressive, and I therefore decided to implement it.
The code example of the third version
String psql = "INSERT INTO CSANSWER VALUES (nextval('CSANSWER_ID'),?,?,?,?,?);";
csaps = connection.prepareStatement(psql);
....
connection.setAutoCommit(false);
for () {
csaps.setInt(1, getTestId());
csaps.setInt(2, getQuestionnaireId());
csaps.setInt(3, getId());
csaps.setInt(4, itemid);
csaps.setInt(5, groupid);
csaps.addBatch();
}
csaps.executeBatch();
connection.commit();
connection.setAutoCommit(true);
Useful links:
Batch Updates
Prepared Statement With Batch Update
