7

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?

fernandohur
  • 7,014
  • 11
  • 48
  • 86
  • how much time does it take to run it vi a CLI or using a tool ? – Shamis Shukoor Nov 22 '12 at 01: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 Nov 22 '12 at 01:19
  • 2
    So I dont think its anything to do with JAVA, I think your server is slow. – Shamis Shukoor Nov 22 '12 at 01:21
  • Maybe your constraints are causing it to take a long time? Have you run Explain and then your query? – Hiro2k Nov 22 '12 at 01:22
  • So you are saying it's a hardware thing? Is there any way I can make it faster? – fernandohur Nov 22 '12 at 01:23
  • @Hiro2k, isn't EXPLAIN only for SELECT statements? – fernandohur Nov 22 '12 at 01:24
  • http://dev.mysql.com/doc/refman/5.6/en/explain.html Apparently only in the newest versions of MySQL :( – Hiro2k Nov 22 '12 at 01:25
  • @Woojah Yes since the mysql workbench also takes about the same time its something to do with the hardware. To make it faster increase the RAM of the server or something – Shamis Shukoor Nov 22 '12 at 01:25
  • @sheldonCooper, it says that a small instance (like mine) has the following: Small DB Instance: 1.7 GB memory, 1 ECU (1 virtual core with 1 ECU), 64-bit platform, Moderate I/O Capacity. It shouldn't be that slow, or am I mistaken? – fernandohur Nov 22 '12 at 01:32
  • @Woojah Firstly how often do you do these inserts ? and why 40 inserts at a time ? Isnt there any alternative ? – Shamis Shukoor Nov 22 '12 at 01:34
  • Micro instances on EC2 are usually CPU throttled. Run `top`, and it might give a hint as to whether mysql is CPU bound or not. Not sure if your small instance is micro instance, or a standard instance. – Vineet Reynolds Nov 22 '12 at 01:35

1 Answers1

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.

GreyBeardedGeek
  • 29,460
  • 2
  • 47
  • 67