The database uses SQLServer, JDK version 1.8, and runs in the SpringBoot environment to compare the three available methods

  • Execute a single insert statement repeatedly

  • XML stitching SQL

  • Batch execution

First, conclusion: Use a small number of inserts to insert a single piece of data repeatedly for convenience. If the number is large, use batch processing. (You can consider the required amount of insert data about 20 as the limit, in my test and database environment is a hundred milliseconds of time, convenience is the most important).

Never use XML concatenation of SQL.

code

Concatenation of SQL to XML

NewId () is the FUNCTION sqlServer uses to generate uuIds and is irrelevant to this article

<insert id="insertByBatch" parameterType="java.util.List"> INSERT INTO tb_item VALUES <foreach collection="list" item="item" index="index" separator=","> (newId(),#{item.uniqueCode},#{item.projectId},#{item.name},#{item.type},#{item.packageUnique}, #{item.isPackage},#{item.factoryId},#{item.projectName},#{item.spec},#{item.length},#{item.weight}, #{item.material},#{item.setupPosition},#{item.areaPosition},#{item.bottomHeight},#{item.topHeight}, #{item.serialNumber},#{item.createTime}</foreach> </insert> Public interface ItemMapper extends Mapper<Item> {int insertByBatch(List<Item> itemList); }Copy the code

The Service class

@Service public class ItemService { @Autowired private ItemMapper itemMapper; @Autowired private SqlSessionFactory sqlSessionFactory; @transactional public void add(List<Item> itemList) {SqlSession session = sqlSessionFactory.openSession(ExecutorType.BATCH,false); ItemMapper mapper = session.getMapper(ItemMapper.class); for (int i = 0; i < itemList.size(); i++) { mapper.insertSelective(itemList.get(i)); If (I %1000==999){session.com MIT (); session.clearCache(); } } session.commit(); session.clearCache(); } / / stitching SQL @ Transactional public void add1 (List < Item > itemList) {itemList. InsertByBatch (itemMapper: : insertSelective); } / / cycle insert @ Transactional public void add2 (List < Item > itemList) {itemList. ForEach (itemMapper: : insertSelective); }}Copy the code

The test class

@RunWith(SpringRunner.class) @SpringBootTest(webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT, classes = ApplicationBoot.class) public class ItemServiceTest { @Autowired ItemService itemService; private List<Item> itemList = new ArrayList<>(); Public void createList(){String json ="{\n" + "\"areaPosition\": TEST \ "\", \ n "+" \ "bottomHeight \" : 5, \ n "+" \ "factoryId \" : \ \ "0", "+" \ \ n "length \" : 233.233, \ n "+" \ "material \" : \"Q345B\",\n" + " \"name\": \"TEST\",\n" + " \"package\": false,\n" + " \"packageUnique\": \"45f8a0ba0bf048839df85f32ebe5bb81\",\n" + " \"projectId\": 094 b5eb5e0384bb1aaa822880a428b6d \ "\", \ n "+" \ "projectName \" : \ "project _TEST1 \", \ n "+" \ "serialNumber \" : 1/2 \ "\", \ n "+" \ "setupPosition \" : \ "column 1 b \", \ n "+" \ "spec \" : \ "200 x200x200 \", \ n "+" \ "topHeight \" : 10,\n" + " \"type\": \"Steel\",\n" + " \"uniqueCode\": \"12344312\",\n" + " \"weight\": 100\n" + " }"; Item test1 = JSON.parseObject(json,Item.class); test1.setCreateTime(new Date()); for (int i = 0; i < 1000; I++) {// the test will change this amount itemlist.add (test1); }} @test@transactional public void tesInsert() {itemService.add(itemList); } @test@transactional public void testInsert1(){itemService.add1(itemList);} @test@transactional public void testInsert1(){itemService.add1(itemList); } // Loop insert @test@transactional public void testInsert2(){itemService.add2(itemList); }}Copy the code

Test results:

After many tests, the 10 and 25 data inserts have great volatility, but they are basically at the level of 100 milliseconds

One way of joining together the SQL in the insert article 500 and article 1000 times wrong (seems to be that the SQL statement is too long, this has to do with the database type, do not make other database testing) : com. Microsoft. Essentially. JDBC. SQLServerException: The tabulated Data Flow (TDS) remote Procedure call (RPC) protocol flow passed in is incorrect. Too many parameters are provided in this RPC request. The maximum should be 2100

You can find

  • The time complexity of the loop insertion is O(n), and the constant C is large

  • The time complexity for concatenation SQL inserts is (should be) O(logn), but the number of successful completions is small and uncertain

  • The time complexity of batch efficiency is O(logn) and the constant C is relatively small

conclusion

While looping in a single piece of data is extremely inefficient, it requires very little code. In the case of tk.mapper’s plug-in, only code is needed:

@Transactional
public void add1(List<Item> itemList) {
    itemList.forEach(itemMapper::insertSelective);
}

Copy the code

Therefore, it must be used in situations where the amount of data required to be inserted is small.

XML concatenation of SQL is the least recommended approach because it requires large chunks of XML and SQL statements to write, which is error prone and inefficient. The point is, although efficiency is ok, when you really need to be efficient, what’s the use of you?

Batch execution is recommended and convenient when there is a large amount of data inserted.