Performance Comparison of Single Insert, XML SQL Concatenation, and Batch Processing in SpringBoot with SQL Server

The author, Lei, compares three ways to insert records into a SQL Server database in a SpringBoot environment (JDK 1.8): repeatedly executing single‑row INSERT statements, constructing SQL via XML concatenation, and using batch processing.

Conclusion : For a small number of rows, repeated single inserts are convenient; for larger volumes (around 20 rows or more in the author's tests) batch processing is far more efficient. XML‑based SQL concatenation should never be used because it is error‑prone and can exceed SQL Server’s parameter limit.

Code examples :

<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);
}

Service class with three insertion strategies:

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

    // Batch processing
    @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.commit();
                session.clearCache();
            }
        }
        session.commit();
        session.clearCache();
    }

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

    // Simple loop insertion
    @Transactional
    public void add2(List<Item> itemList) {
        itemList.forEach(itemMapper::insertSelective);
    }
}

Test class that generates a list of 1,000 items and measures insertion time for each method:

@RunWith(SpringRunner.class)
@SpringBootTest(webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT, classes = ApplicationBoot.class)
public class ItemServiceTest {
    @Autowired
    ItemService itemService;
    private List<Item> itemList = new ArrayList<>();

    @Before
    public void createList() {
        // build a sample Item object from JSON and add 1000 copies
        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); }
}

Test results : In repeated runs, inserting 10–25 rows took a few hundred milliseconds; batch processing remained consistently fast. The XML‑concatenated method failed when inserting 500 or 1,000 rows with a SQLServerException indicating the RPC request exceeded the maximum of 2100 parameters.

Observations :

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

XML concatenation appears to have O(log n) complexity but is unreliable.

Batch processing also shows O(log n) complexity with a smaller constant, making it the preferred approach for large data sets.