How to reduce time taken to insert 100000 records in database using PreparedStatement batch ?

[Ganish Patil]

/*
 A program to insert 100000 new employee information into employeesinfo table using PreparedStatement batch update.

employeeinfo table structure:   
column             datatype
empid               int
empname         varchar(20);
age                   int 11
gender              char(10) 

database type =mysql
server = localhost
userdatabase = mywork
userid = root
password = dbpass

*/
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.io.IOException;
import java.io.BufferedReader;

class InsertPreparedBatch5EmployeeInfo{


// Method which creates connection
    private Connection establishConnection(){
        Connection conn = null;
        String databaseType, serverName, userDatabaseName, userId, password;
        databaseType = "mysql";
        serverName = "localhost";
        userDatabaseName = "mywork";
        userId = "root";
        password = "dbpass";
        try{
            //Class.forName("com.mysql.jdbc.Driver"); No need since JDBC 4.0 
            conn = DriverManager.getConnection( "jdbc:" + databaseType + "://" + serverName + "/" + userDatabaseName, userId, password);
        }
        catch(SQLException e){
            PrintSQLExceptions.printSQLExceptions(e);    
        }
    return conn;
    }   
    // Method inserts records in database
    private void insertInfo(Connection conn, int empid, String empname, int empage, String gender)throws SQLException{
        PreparedStatement pstmt = null;
        String query;
        query = "insert into employeeinfo values(?,?,?,? );";
        try{
            conn.setAutoCommit(false);
            pstmt = conn.prepareStatement(query);
            long startTime, endTime, diffTime;
            startTime = System.currentTimeMillis();
            int totalRecords = 100000; // No of records to insert in table employeeinfo.
            int cnt = 1; 
            /*  cnt as counter used as empid for temp use, 
                 rest fields name, age and gender taken once from user and used for all row .
            */
            // for Loop which adds 5 records batch at a time.
            for (int i = 0; i< (totalRecords/5); i++){
                empid = cnt;
                pstmt.setInt(1,empid);
                pstmt.setString(2,empname);
                pstmt.setInt(3,empage);
                pstmt.setString(4,gender);
                pstmt.addBatch();     

                empid = ++cnt;;
                pstmt.setInt(1,empid);
                pstmt.setString(2,empname);
                pstmt.setInt(3,empage);
                pstmt.setString(4,gender);
                pstmt.addBatch();     

                empid = ++cnt;;
                pstmt.setInt(1,empid);
                pstmt.setString(2,empname);
                pstmt.setInt(3,empage);
                pstmt.setString(4,gender);
                pstmt.addBatch();     

                empid = ++cnt;;
                pstmt.setInt(1,empid);
                pstmt.setString(2,empname);
                pstmt.setInt(3,empage);
                pstmt.setString(4,gender);
                pstmt.addBatch();     

                empid = ++cnt;;
                pstmt.setInt(1,empid);
                pstmt.setString(2,empname);
                pstmt.setInt(3,empage);
                pstmt.setString(4,gender);
                pstmt.addBatch();     

                pstmt.executeBatch(); 
                // Clears batch after 5000 records.  
                if(cnt % 5000 == 0){
                    System.out.println("Batch Cleared!");
                    pstmt.clearBatch();
                }
                 ++cnt;  
           }//  for loop ends
                 conn.commit();
                 conn.setAutoCommit(true);
                 //  Performance time calculated
                 endTime = System.currentTimeMillis();
                 diffTime = endTime  - startTime; 
                 System.out.println("Record no:" + (cnt) + " added to batch !");                 
                 System.out.println("Insertion start time in milli seconds: "+ startTime);
                 System.out.println("Insertion start time in milli seconds: "+ endTime);
                 System.out.println("Total time spent to insert "+ totalRecords + " records is: " + diffTime + " milliseconds");
        }catch(SQLException e){
                PrintSQLExceptions.printSQLExceptions(e);  
                if (conn != null) {
                    try {
                        System.err.print("Transaction is being rolled back");
                        conn.rollback();
                    }catch(SQLException excep) {
                        PrintSQLExceptions.printSQLExceptions(e);
                    }
                }
        }
        finally {
            if (conn != null) {
                conn.close();
            }
            if (pstmt != null) {
                pstmt.close();
            }
       
        }
    }
    public static void main(String args[])throws SQLException{
        /*
             GetBufferedReaderObject.getBufferedReaderObject() getBufferedReaderObject() method of
             class GetBufferedReaderObject returns an object of BufferedReader.               
        */
        BufferedReader brReader = GetBufferedReaderObject.getBufferedReaderObject();
        String name, gender;
        int empid, age;
        try{
            System.out.println("Enter empid");
            empid = Integer.parseInt( brReader.readLine());
            System.out.println("Enter employee name");
            name = brReader.readLine();
            System.out.println("Enter employee age");
            age = Integer.parseInt( brReader.readLine());
            System.out.println("Enter employee gender male/female");
            gender = brReader.readLine();
            InsertPreparedBatch5EmployeeInfo empObj1 = new InsertPreparedBatch5EmployeeInfo();
            // Call to data inserting method
            empObj1.insertInfo(empObj1.establishConnection(),empid, name, age, gender );
        }
        catch(IOException e){ 
            System.out.println(e);
        }   
    }
}

This program runs successfully.
It's taking 14 seconds to insert 100000 simple records of 4 columns i.e empid, empname, age, gender. How can I reduce the timing even records are complex and more than 100000 ?

[Tapas Chand]

Depending on the memory assigned to Java and network speed between Java and DB, batch size can be selected.
I will not set a batch size to 10 as it will do a lot of iterations between the network, and I will not set a batch size of 10000 to avoid out of memory error and this will put a lot of data in network at a time.
You can go for batch size of 500 so that it will be a balance between network iteration and avoid out of memory error.
So after every 500th addBatch(), do an executeBatch().
Again, there is no hard n fast rule, optimum-performance-batch-size can vary depending on factors.

[Knute Snortum]

I'll copy this to the JDBC forum to see if we can get more responses.

[Ganish Patil]

Apologies for delay, had internet problem. Here in this example I made a batch of 5 records. After this I called executeBatch(). so

The list of commands associated with stmt will now be empty because the four commands added previously were sent to the database when stmt called the method executeBatch. You can at any time explicitly empty this list of commands with the method clearBatch.

JLS link still after inserting 5000 records means 1000 batch I'm explicitly clearing batch commands.

So after every 500th addBatch(), do an executeBatch().

do you mean after 500 records ? because what I'm thinking is

                empid = cnt;
                pstmt.setInt(1,empid);
                pstmt.setString(2,empname);
                pstmt.setInt(3,empage);
                pstmt.setString(4,gender);
                pstmt.addBatch();     
 
                empid = ++cnt;;
                pstmt.setInt(1,empid);
                pstmt.setString(2,empname);
                pstmt.setInt(3,empage);
                pstmt.setString(4,gender);
                pstmt.addBatch();     
 
                empid = ++cnt;;
                pstmt.setInt(1,empid);
                pstmt.setString(2,empname);
                pstmt.setInt(3,empage);
                pstmt.setString(4,gender);
                pstmt.addBatch();     
 
                empid = ++cnt;;
                pstmt.setInt(1,empid);
                pstmt.setString(2,empname);
                pstmt.setInt(3,empage);
                pstmt.setString(4,gender);
                pstmt.addBatch();     
 
                empid = ++cnt;;
                pstmt.setInt(1,empid);
                pstmt.setString(2,empname);
                pstmt.setInt(3,empage);
                pstmt.setString(4,gender);
                pstmt.addBatch();     
 

above five pstmt.addBatch() is one batch of 5 records hope I'm correct ? and inserting them once when I call executeBatch() so one batch of 5 records right ?

[Ganish Patil]

do you mean I should put this code instead of code pstmt.executeBatch(); on line no 101 in loop?

if (cnt % 500 = 0){
    pstmt.executeBatch();
}

[Tapas Chand]

yes exactly.

[Ganish Patil]

yes I did that still takes 14074 milliseconds. Do I have to use procedures here. I used that many moons ago but right now I don't remember its usage. will figure it out once I finish with Servlet and JSP.
Why I'm curious about this stuff is because when I was working as trainee in a company I had a client project in which I had to insert 80000- 90000 records in a table, these records were result of executing select SQL query from different tables, using batch it used to take 17 minutes something like that. By the time I could solve that the senior found the solution but I didn't how he solved. anyway if you find solution please let me know, Thank you.

[Tapas Chand]

Stored Procedure is not a solution in this scenario. They are meant for different purpose.
By the way 14 secs(only for insert) sound OK to me.

[Ganish Patil]

ya I think so.