Java - Why are my inserts so slow?

Question

Somehow my MySQL Inserts are really slow. Even when I use "Batch-Inserts". About 50 inserts take up to 2000ms.. Is this normal or is there a way to improve my code?

 String sql = "INSERT INTO wlw ( wlw_level, wlw_name, wlw_url, wlw_processing, wlw_finished, wlw_source )" + "VALUES(?, ?, ?, ?, ?, ?)"; PreparedStatement preparedStatement = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); for(QueryStore qs : QueryStore) { preparedStatement.setInt(1, qs.level); preparedStatement.setString(2, qs.anchor); preparedStatement.setString(3, qs.url); preparedStatement.setInt(4, qs.processing); preparedStatement.setInt(5, qs.finished); preparedStatement.setLong(6, qs.id); preparedStatement.addBatch(); System.out.println("Adding URL: " + qs.url); } System.out.println("Start saving ..."); long then = System.currentTimeMillis(); preparedStatement.executeBatch(); long now = then - System.currentTimeMillis(); System.out.println("SAVING END - Took me " + now + "ms");

Thanks for any suggestions!!

One possible optim. is not using Statement.RETURN_GENERATED_KEYS as you don't use the returned generated keys. — user180100
– user180100, Commented Apr 22, 2013 at 20:45
Are you using a connection pool or creating the connection manually every time you call the method which execute the INSERT operation? Has your table any trigger on insert or is any monitor for your tables? How many data are you inserting? How many indexes your table manages? Does it use lot of foreign keys? — Luiggi Mendoza
– Luiggi Mendoza, Commented Apr 22, 2013 at 20:54
@Crayl i think its a problem because when you insert a row into an InnoDB table, innodb engin build the pk and update indexes for that row, and this can slow down your inserts considerably — Khalil
– Khalil, Commented Apr 22, 2013 at 21:21

Joshua Martell · Accepted Answer · 2013-04-22 20:45:39Z

5

Try adding rewriteBatchedStatements=true to your connection parameters.

answered Apr 22, 2013 at 20:45

Joshua Martell

7,2122 gold badges33 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Crayl Over a year ago

Amazing. Now it's very fast. Thank you very much!

Collectives™ on Stack Overflow

Java - Why are my inserts so slow?

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related