JDBC Batch executing extremely slow

Question

Can anyone tell me what I'm doing wrong I'm executing 350 inserts in a mysql and it's taking like 40 secs.

Here is the code

long t0 = System.currentTimeMillis(); Connection con = connectionProvider.getConnection(); PreparedStatement s = con.prepareStatement("insert into domkee.friends(idFriends,friend1Id,friend2Id,friend2Name) values(?,?,?,?)"); con.setAutoCommit(false); for (Friend f : friends) { s.setLong(1, 0); s.setLong(2, f.getFriend1Id()); s.setLong(3, f.getFriend2Id()); s.setString(4, f.getFriend2Name()); s.addBatch(); } long t1 = System.currentTimeMillis() - t0; s.executeBatch(); long t2 = System.currentTimeMillis()-t0; con.commit(); long t3 = System.currentTimeMillis()-t0; s.close(); con.close(); long t4 = System.currentTimeMillis()-t0; System.out.println(((double)t1/1000) + ";" + ((double)t2/1000) + ";" + ((double)t3/1000) + ";" + ((double)t4/1000));

and here is the console:

0.156;39.251;39.376;39.486

So the .executeBatch() is taking like 40 secs, what could be the problem?

how much time does it take to run it vi a CLI or using a tool ? — Shamis Shukoor
– Shamis Shukoor, Commented Nov 22, 2012 at 1:14
Well from MySQL Workbench it takes about the same (39-42 secs). In case it's relevant the server is a MySQL 5 running on a small instance in AWS — fernandohur
– fernandohur, Commented Nov 22, 2012 at 1:19
So I dont think its anything to do with JAVA, I think your server is slow. — Shamis Shukoor
– Shamis Shukoor, Commented Nov 22, 2012 at 1:21
Maybe your constraints are causing it to take a long time? Have you run Explain and then your query? — Hiro2k
– Hiro2k, Commented Nov 22, 2012 at 1:22
So you are saying it's a hardware thing? Is there any way I can make it faster? — fernandohur
– fernandohur, Commented Nov 22, 2012 at 1:23

GreyBeardedGeek · Accepted Answer · 2017-10-27 11:47:48Z

18

Add ?rewriteBatchedStatements=true to the end of your JDBC url. It'll give you a serious performance improvement. Note that this is specific to MySql, won't have any effect with any other JDBC drivers.