MySQL BULK Insert vs. Single Insert efficiency comparison

1. Data insertion Performance (single insert and BULK INSERT) [Java]View PlainCopy 

2. Public class Test {
6. private Long ID;
10. private String test;
14. Public Long getId () {
16. return ID;
18. }
22. public void SetId (Long id) {
24. this.id = ID;
26. }
30. Public String Gettest () {
32. return test;
34. }
38. public void Settest (String test) {
40. this.test = test;
42. }
44. }

Mapper.xml file

[HTML]View PlainCopy 

2. <? XML version= "1.0" encoding="UTF-8" ?>
4. <!  DOCTYPE Mapper Public "-//mybatis.org//dtd mapper 3.0//en" "Http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
6. <mapper namespace="Com.astrospace.test.mapper.TestMapper" >
8. <resultmap id="test" type="Com.astrospace.test.dmo.Test" >
10. <ID property= "id" column="id"/>
12. <result property="test" column="test" />
14. </resultmap>
18. <insert id="Add" parametertype="Com.astrospace.test.dmo.Test">
20. INSERT into TEST (id,test) VALUES (#{id},#{test});
22. </Insert>
26. <insert id="Batchadd" parametertype="java.util.List">
28. INSERT into TEST (id,test)
30. VALUES
32. <foreach collection="list" item="item" index="index" separator="," >
34. (#{item.id},#{item.test})
36. </foreach>
38. </Insert>
42. </mapper>

Call the Add and Batchadd methods.

Different data volumes were tested 5 times and the results were as follows:

50,000 data per insert Average time: 233748ms
Bulk Insert 50,000 data average time: 2590ms
Contrast: 50 times times less efficient
10,000 data per insert Average time: 22036ms
Bulk INSERT 10,000 data average time: 3330ms
Contrast: 6 times times less efficient
1000 data per insert Average time: 3122ms
BULK INSERT 1000 Data average time: 374ms
Contrast: 8 times times less efficient

The more data, the more efficient it becomes.

MySQL BULK Insert vs. Single Insert efficiency comparison