Java MySQL bulk insert vs normal

Question

I tried to make the insert to mysql faster, so I werote a code with bulk insert. The problem is that when I run the following code, results are the same (Time elapsed: 2min, or 173sec) for both methods. Any comment on the matter is welcome.

MySQLConn mysqlconn = new MySQLConn(db,username,pass);
    Connection conn =mysqlconn.getConnection();
    PreparedStatement ps = null;

    String query = "insert into table (column) values (?)";

    System.out.println("bulk insert started with "+10000 );
    long startTime = System.currentTimeMillis();

    try {
        ps = conn.prepareStatement(query);
        long start = System.currentTimeMillis();
        for(int i =0; i<10000;i++){

            ps.setString(1, "Name"+i);

            ps.addBatch();

            if(i%1000 == 0) ps.executeBatch();
        }
        ps.executeBatch();

        System.out.println("Time Taken="+(System.currentTimeMillis()-start));

    } catch (SQLException e) {
        e.printStackTrace();
    }

    System.out.println("End inserting data...");

    long stopTime = System.currentTimeMillis();
    long elapsedTime = (stopTime - startTime); 

    long days = TimeUnit.MILLISECONDS.toDays(elapsedTime);
    long hours = TimeUnit.MILLISECONDS.toHours(elapsedTime);
    long minutes = TimeUnit.MILLISECONDS.toMinutes(elapsedTime);
    long seconds = TimeUnit.MILLISECONDS.toSeconds(elapsedTime);


    System.out.println("Time elapsed: "+minutes+"min, or "+seconds+"sec");



    System.out.println("normal insert started with "+10000 );

    long startTimeNormal = System.currentTimeMillis();

    for(int i=0;i<10000;i++){ 
        String ins="Data"+i;
        Insert insert = new Insert(conn,ins,"db.table", "column" );
    }
    System.out.println("End inserting data...");

    long stopTimeNormal = System.currentTimeMillis();
    long elapsedTimeNormal = (stopTimeNormal - startTimeNormal); 

    long daysN = TimeUnit.MILLISECONDS.toDays(elapsedTime);
    long hoursN = TimeUnit.MILLISECONDS.toHours(elapsedTime);
    long minutesN = TimeUnit.MILLISECONDS.toMinutes(elapsedTime);
    long secondsN = TimeUnit.MILLISECONDS.toSeconds(elapsedTime);


    System.out.println("Time elapsed: "+minutesN+"min, or "+secondsN+"sec");

Answer 1

You use addBatch and executeBatch, which is good, but you also need to set autocommit on your connection to false in order to achieve faster execution times.

Something along the lines:

Connection conn = mysqlconn.getConnection();
conn.setAutoCommit(false); // here

PreparedStatement ps = null;

String query = "insert into table (column) values (?)";

System.out.println("bulk insert started with "+10000 );
long startTime = System.currentTimeMillis();

try {
    ps = conn.prepareStatement(query);
    long start = System.currentTimeMillis();
    for(int i =0; i<10000;i++){

        ps.setString(1, "Name"+i);

        ps.addBatch();

        if(i%1000 == 0) {
            ps.executeBatch();
            conn.commit(); // here
        }
    }
    ps.executeBatch();
    conn.commit(); // here

    System.out.println("Time Taken="+(System.currentTimeMillis()-start));

} catch (SQLException e) {
    e.printStackTrace();
}