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");

in both cases the time was 2 minutes OR 173 seconds (2 minutes is the long minutesN = TimeUnit.MILLISECONDS.toMinutes(elapsedTime); — George Violettas
– George Violettas, Commented Apr 29, 2015 at 9:46

Uooo · Accepted Answer · 2015-04-29 09:52:50Z

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();
}

answered Apr 29, 2015 at 9:52

Uooo

6,3748 gold badges39 silver badges64 bronze badges

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

1 Comment

George Violettas Over a year ago

WOOOOOOOOOOOOOOOOAOOOOU YEEESSS. It takes 1 second !!!! YES, 1 second for 10.000 inserts.... THANKSSS

krstf · Accepted Answer · 2015-04-29 09:56:58Z

1

You need to set autocommit to false

conn.setAutoCommit(false);

And execute a commit manually after each executeBath()

example

answered Apr 29, 2015 at 9:56

krstf

6278 silver badges25 bronze badges

Collectives™ on Stack Overflow

Java MySQL bulk insert vs normal

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related