[tweet-dzone]When you have to deal with a large amount of data to be operated on mySQL databases, the performance can be dramatically improved using a few simple tweaks.
First of all you have to use Statement.addBatch/executeBatch instead of simple execute methods. For each added batch, the jdbc driver will store in local memory and when the executeBatch is invoked, all the batches are sent at once to the database. This will result in an huge speed improvement.
When you deal with such operations you should keep an eye one the memory allocated to the java process. The JDBC driver(Connector/J) will use the heap memory to build the batch until is executed. In order to make sure you don't run out of memory you have to executeBatch method from time to time.
When you deal with mySQL database you can make and additional speed improvement. This can be applied for the cases when you have to insert values in the database(which is the probably the case because 90% of large amount data operations are imports).
When you configure the JDBC connection with "rewriteBatchedStatements=true", the driver takes the statements in the form "INSERT INTO foo VALUES (...)" and rewrites them as "INSERT INTO foo VALUES (...), (...), (...)". You can see here benchmarks which records 10x performance increase. Yu have to make sure you have the latest Connector/J version to to squeeze the best performance out of it(at least 5.1.8).
static protected String INSERT_GAME_SQL =
"INSERT INTO tablename(name, category, title, description"
", metascore) VALUES(?, ?, ?, ?, ?)";
public void batchInsert()
throws SQLException {
try
{
Class.forName("com.mysql.jdbc.Driver").newInstance();
connection = DriverManager.getConnection(
connectionString + "?rewriteBatchStatements=true",user,passwd);
connection.setAutoCommit(false);
PreparedStatement statement = connection.prepareStatement(
INSERT_SQL_STATEMENT);
for (int i = 0; i < limit; i++)
{
statement.setString(1, "value1");
statement.setString(2, "value2");
statement.setString(3, "value3");
statement.setString(4, "value4");
statement.setInt(5, 134);
statement.addBatch();
if ( i % 1000 == 0) {
statement.executeBatch();// Execute every 1000 items
}
}
statement.executeBatch();
connection.commit();
}
catch(SQLException e)
{
connection.rollback();
throw e;
}
finally
{
// if not required anymore:
//close statement
//close connection
}
}