Java 3 Kinds of BULK INSERT update operation efficiency Horizontal comparison __java

Used to say that the efficiency of bulk inserts and updates is higher than that of non batches, but how much higher, not evaluated, today I'm going to test the specific (1) Three kinds of insert operation method 1.1 use a For loop BULK insert

Sample XML

    <insert id= "Insertuser" >
        insert INTO Test_user (u_name,create_date) value (#{username},sysdate ())
    < /insert>

Sample code:

for (int i = 1; I <= num i++) {
    User user = new user ();
    User.setusername ("a" + i);
    User.setcreatedate (New Date ());
    Userdao.insertuser (user);
}

1.2 using JDBC

Sample code:

        Connection Conn;
        try {
            class.forname ("Com.mysql.jdbc.Driver");
            conn = Drivermanager.getconnection ("Jdbc:mysql://192.168.0.200:3306/xxx", "root", "root");
            Conn.setautocommit (false);
            String sql = "INSERT into Test_user (u_name,create_date) value (?, Sysdate ())";
            PreparedStatement prest = conn.preparestatement (sql, resultset.type_scroll_sensitive,
                    Resultset.concur_read_ only);
            Conn.setautocommit (false);

            for (int i = 1; I <= i++) {
                prest.setstring (1, "a" + i);
                Prest.addbatch ();
            }
            Prest.executebatch ();
            Conn.commit ();


            Conn.close ();
        } catch (Exception ex) {
            ex.printstacktrace ();
        }

1.3 Bulk Insert method using MyBatis

is actually based on a list stitching into a SQL
Sample XML

    <insert id= "Batchinsertlist" >
        insert INTO Test_user (u_name,create_date)
        values
        <foreach item= "Item" index= "index" collection= "userlist" separator= "," >
            (#{item.username},sysdate ())
        </foreach >
    </insert>

Sample code

        list<user> userlist = new arraylist<user> ();

        for (int i = 1; I <= num i++) {
            User user = new user ();
            User.setusername ("a" + i);
            User.setcreatedate (New Date ());
            Userlist.add (user);
        }
        Userdao.batchinsertlist (userlist);

The amount of data is 10,100,300,1000,5000 bar data, respectively.

Quantity Level: 10
Bulk Insert time consuming: 141
Non-batch insert time consuming: 93
JDBC BULK Insert time consuming: 195

Quantity Level: 100
Bulk INSERT time consuming: 164
Non-batch Insert time consuming: 970
JDBC BULK INSERT time consuming: 718

Quantity Level: 300
Bulk Insert time consuming: 355
Non-batch insert time consuming: 3030
JDBC BULK Insert time consuming: 1997

Quantity Level: 500
Bulk INSERT time consuming: 258
Non-batch insert time consuming: 5355
JDBC BULK Insert time consuming: 2974

Quantity Level: 1000
Bulk INSERT time consuming: 422
Non-batch insert time consuming: 8787
JDBC BULK Insert time consuming: 6440

Quantity Level: 5000
Bulk INSERT time consuming: 870
Non-batch insert time consuming: 43498
JDBC BULK Insert time consuming: 30368

Overall it seems that the efficiency of the sort
MyBatis BULK Insert > JDBC BULK INSERT > Loop invoke INSERT statement (2) Three batch Update method 2.1 Use for loop batch update

Sample XML

    <update id= "UpdateUser" >
        update test_user set test_user.u_name = (#{updateusername}) where Test_user.u_name = (#{username})
    </update>

Sample code

        for (int i = 1; I <= num i++) {
            User user = new user ();
            User.setusername ("a" + i);
            User.setupdateusername ("B" + i);
            Userdao.updateuser (user);
        }

2.2 JDBC Batch update

Sample code

Connection Conn;
        try {
            class.forname ("Com.mysql.jdbc.Driver");
            conn = Drivermanager.getconnection ("Jdbc:mysql://192.168.0.200:3306/xxx", "root", "root");
            Conn.setautocommit (false);

            Save the current autocommit mode
            boolean autocommit = Conn.getautocommit ();
            Turn off autocommit
            Conn.setautocommit (false);
             Statement stmt =conn.createstatement (resultset.type_scroll_sensitive,resultset.concur_read_only); 

            for (int i = 1; I <= num i++) {
                stmt.addbatch ("update test_user set test_user.u_name = (' d" +i+ ') where Test_user . U_name = (' C "+i+") "); 
            }

            Stmt.executebatch ();   
            Conn.commit (); 
            Conn.close ();
        } catch (Exception ex) {
            ex.printstacktrace ();
        }

2.3 mybatis Batch update

is actually taking advantage of MySQL's batch update syntax
The syntax of the case
See "Case when Syntax"
Sample XML

    <update id= "Batchupdatelist" >
        update test_user
        <trim prefix= "Set" suffixoverrides= "," >

            <trim prefix= "U_name =case" suffix= "End," >
                <foreach item= "item" collection= "UserList" > When
                    Test _user.u_name = (#{item.username})
                    then #{item.updateusername}
                </foreach>
            </trim>

        < /trim>
        where
        <foreach item= "item" collection= "UserList" separator= "or" >
            (test_user.u_name = (#{item.username}))
        </foreach>

    </update>

Sample code

        for (int i = 1; I <= num i++) {
            User user = new user ();
            User.setusername ("a" + i);
            User.setupdateusername ("B" + i);
            Userlist.add (user);
        }
        Userdao.batchupdatelist (userlist);

The amount of data is 10,100,300,1000,5000 bar data, respectively.

Number of data: 10
Batch update time consuming: 279
Non-batch update time consuming: 1522
JDBC Batch update time consuming: 255

Number of data: 100
Batch update time consuming: 720
Non-batch update time consuming: 3391
JDBC Batch update time consuming: 1912

Number of data: 300
Batch update time consuming: 987
Non-batch update time consuming: 9827
JDBC Batch update time consuming: 7616

Number of data: 500
Batch update time consuming: 1649
Non-batch update time consuming: 16253
JDBC Batch update time consuming: 10475

Number of data: 1000
Batch update time consuming: 2552
Non-batch update time consuming: 33048
JDBC Batch update time consuming: 20793

Number of data: 5000
Batch update time consuming: 19066
Non-batch update time consuming: 239127
JDBC Batch update time consuming: 103273

Comprehensive analysis, the efficiency of the following ranking
MyBatis Batch update > JDBC Batch update > loop call UPDATE statement