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.

edited Oct 27, 2017 at 11:47

answered Nov 22, 2012 at 2:08

GreyBeardedGeek

30.3k2 gold badges51 silver badges71 bronze badges

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

7 Comments

jtahlborn Over a year ago

more details slava-technical.blogspot.com/2010/07/…

Ironluca Over a year ago

The above suggestion is fantastic

Sibidharan Over a year ago

@jtahlborn That blog is for invite only readers. Please fix it. Or make it open.

jtahlborn Over a year ago

@Sibidharan i don't control the blog post. there's plenty of other places on the web with more details.

Abdul Over a year ago

hi , I Tried using this solution with ORacle, Not able to use. Please help. stackoverflow.com/questions/46969005/…

|

Collectives™ on Stack Overflow

JDBC Batch executing extremely slow

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related