Comparing Three Methods of Inserting Data into SQL Server with SpringBoot

The author uses a SpringBoot project with JDK 1.8 and SQL Server to compare three insertion techniques: repeatedly executing single INSERT statements, constructing SQL via XML, and batch processing with MyBatis.

Conclusion: For small amounts of data, repeatedly inserting single rows is convenient; for larger volumes (around 20 rows or more), batch processing is preferred due to its lower overhead, while XML‑based SQL should be avoided.

Code

XML Insert (MyBatis)

<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>

The corresponding Mapper interface extends tk.mybatis.mapper.common.Mapper<Item> and declares int insertByBatch(List<Item> itemList);

Service Class

@Service
public class ItemService {
    @Autowired
    private ItemMapper itemMapper;
    @Autowired
    private SqlSessionFactory sqlSessionFactory;

    // Batch processing
    @Transactional
    public void add(List
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) { // commit every 1000 rows to avoid OOM
                session.commit();
                session.clearCache();
            }
        }
        session.commit();
        session.clearCache();
    }

    // XML concatenated SQL (not recommended)
    @Transactional
    public void add1(List
itemList) {
        itemList.insertByBatch(itemMapper::insertSelective);
    }

    // Loop insert
    @Transactional
    public void add2(List
itemList) {
        itemList.forEach(itemMapper::insertSelective);
    }
}

Test Class

@RunWith(SpringRunner.class)
@SpringBootTest(webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT, classes = ApplicationBoot.class)
public class ItemServiceTest {
    @Autowired
    ItemService itemService;

    private List
itemList = new ArrayList<>();

    @Before
    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\": \"094b5eb5e0384bb1aaa822880a428b6d\",\n"
                + "    \"projectName\": \"项目_TEST1\",\n"
                + "    \"serialNumber\": \"1/2\",\n"
                + "    \"setupPosition\": \"1B柱\",\n"
                + "    \"spec\": \"200X200X200\",\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++) {
            itemList.add(test1);
        }
    }

    @Test
    @Transactional
    public void tesInsert() {
        itemService.add(itemList);
    }

    @Test
    @Transactional
    public void testInsert1() {
        itemService.add1(itemList);
    }

    @Test
    @Transactional
    public void testInsert2() {
        itemService.add2(itemList);
    }
}

The tests insert 10, 25, 500, and 1000 rows repeatedly; batch processing consistently stays within a few hundred milliseconds, while XML‑based SQL fails beyond 500 rows due to SQL Server's 2100‑parameter limit.

Performance observations:

Loop insert has O(n) complexity with a large constant factor.

XML concatenated SQL theoretically O(log n) but is unreliable and often fails.

Batch processing offers O(log n) complexity with a smaller constant, making it the most efficient for large data sets.

Final recommendation: use simple loop insertion for small data volumes, avoid XML‑based SQL entirely, and adopt batch processing for medium to large volumes.