how to Insert million data into database

Question

I am trying to inserts 1 million of records into the DB table.

I want to create at least 3 threads that each fires one insert,then we can get 3 parallel requests in every sec.We can control the firing of the inserts to happen exactly at the same time by making each thread wait and wake up together to the same interrupt.Then each thread goes to sleep until that 1 second window elapses.Then the whole process will repeats. how can i accomplish it?

Any suggestion or tips will be helpful.

Answer 1

Inserting large amount of data is considered a bad practice. Insert large amount of data will take lot of time, while you can load data in to table directly using sqlloader or similar tool. These loaders are faster as they will not cause overhead of transaction management.

Here are some questions which I ask myself when I have huge data to be loaded in database.

1. Is this reference/static data ( like country, cities, banks)
2. Is this dynamic data generated by application/generated by use of application( like bills, call logs, payment, account activities).

Most of the cases it is case 1, in that case sqlloader is preferred.

Other cases(2) may arise due to data migration/upgrade, in that case also sqlloader is preferred.

Based on above explanation. You may choose to describe your problem better or choose sqlloader.

Answer 2

This is a quick example of a Batch Insert based on mykong code from.. http://www.mkyong.com/jdbc/jdbc-preparedstatement-example-batch-update/

This basically gives you the speed of sqlloader, which does batch inserts. And only 1 thread should be used.

What I have done here is put the inserts into a loop to show you have to clear the batch every few thousand records..

You would remove the infinite loop and have it insert data instead of the hard coded mkyong data

String insertTableSQL = "INSERT INTO DBUSER"
        + "(USER_ID, USERNAME, CREATED_BY, CREATED_DATE) VALUES"
        + "(?,?,?,?)";

PreparedStatement preparedStatement = dbConnection.prepareStatement(insertTableSQL);
try {
    dbConnection.setAutoCommit(false);

    int batchTotal=0;
    for  (;;) { // infinate loop? change this to get your data here
        preparedStatement.setInt(1, 101);
        preparedStatement.setString(2, "mkyong101");
        preparedStatement.setString(3, "system");
        preparedStatement.setTimestamp(4, getCurrentTimeStamp());
        preparedStatement.addBatch();
        if (batchTotal++ == 4096) {
            int[] result = preparedStatement.executeBatch();
            preparedStatement.clearBatch();
            batchTotal=0;                    
        }
    }
    if (batchTotal > 0) {
        int[] result = preparedStatement.executeBatch();
    }

    dbConnection.commit();
}  finally {
    preparedStatement.close();
}