JDBC batch update using spring Problem

[florin.botis]

Dear gurus,

I have a client application that has to write lots of records (around 1 million) to a MySQL DB. To achieve this I am using the spring framework and the batch update mechanism in the following way:

public void saveSecurities(final ArrayList<ISecurityData> securities) {
getSimpleJdbcTemplate().getJdbcOperations().batchU pdate(
"INSERT INTO wtrader.security_data VALUES(?,..,?)",
new BatchPreparedStatementSetter() {
public int getBatchSize() {
return securities.size();
}

public void setValues(PreparedStatement ps, int index) throws SQLException {
ISecurityData securityData = securities.get(index);

ps.setString(1, securityData.getISIN());
...
ps.setObject(26, securityData.getBonusLevel());
}
});
}

This method is part of a class that extends SimpleJdbcDaoSupport.

The problem is that I see no performance improvement at all - the whole data import takes around the same time as the import without batching.
I am using the mysql connector/j version 5.0.8. According to the documentation, this is supposed to support batching.

Could anybody help? Thanks in advance!

Best regards,
Levente

[Marten Deinum]

Which version of MySQL are you using? Also what kind of tables (MyISAM/InnoDB?) are you using?

Next to that I'm not sure if MySQL supports batching at all.

[florin.botis]

Hi,

I'm using mySql 5.0 and the table is InnoDB.