Usage Scenarios

Scenario Description

Batch insertion offers a more efficient way to write multiple records into the database in a single operation. Compared to single-record insertion, batch insertion decreases interactions between the application and the database, leading to lower network latency and system resource usage, while significantly improving data write efficiency.

This section illustrates various operations using the ODBC driver, including establishing database connections, utilizing transactions, executing batch insertion, and obtaining column information in the result set.

Trigger Conditions

The UseServerSidePrepare and UseBatchProtocol parameters are enabled in the ODBC configurations (both are enabled by default). Batch binding parameters are set and batch data is initialized in the application code. Batch insertion is then executed under these settings.

Impact on Services

Lower network interaction costs
Combining multiple INSERT statements into one batch operation significantly reduces the number of round trips between the client and the database. This enhances the overall throughput and minimizes the impact of network congestion on performance.
Higher data processing efficiency
In single-record insertion, the database must parse the syntax and generate an execution plan for each SQL statement. In contrast, batch insertion requires parsing the syntax and generating the plan only once, eliminating repetitive tasks and saving CPU cycles and memory allocation time.
Reduced system resource usage and overhead
In single-record insertion, transaction commits or Xlog writes occur at least once. In contrast, batch insertion allows multiple records to be inserted within a single transaction, significantly reducing the frequency of transaction commits, Xlog pressure, and transaction management overhead. In addition, it decreases the total number of network packet processing, transaction management, log write, and row format conversion tasks, which in turn lowers the CPU loads and temporary memory usage of the database server. This results in more resources being available for core query and computing operations.
Higher memory usage
When large data sizes are involved, constructing SQL statements for batch insertion can significantly increase memory usage. This is particularly noticeable when you construct SQL statements through string concatenation, as it can lead to a sharp rise in memory consumption. Large-size batch processing may exceed the maximum SQL length limit of the database or driver, or trigger other parameter restrictions, potentially leading to errors or performance issues.

Here is a detailed comparison between batch insertion and single-record insertion.

Mode	Advantages	Disadvantages
Single-record insertion	Its code is simple, straightforward, and easy to implement. If any single record fails, it can be accurately identified and handled without impacting other records. This mode is less demanding in terms of database and driver compatibility.	Extensive network interactions are needed. Each INSERT operation requires connecting, parsing, and committing, leading to suboptimal performance. Inserting a large number of records is likely to cause a bottleneck. Not using transactions may result in failure to guarantee the consistency of INSERT operations.
Batch insertion	This mode greatly reduces the number of network round trips and SQL parsing instances, leading to a notable improvement in insertion throughput. Multiple rows can be committed within a single transaction to guarantee atomicity.	Its code is complex, requiring manual concatenation of placeholders and parameters. If a single statement encounters an error, all data will be rolled back, complicating the error recovery process. The number of placeholders is limited; therefore, it is essential to carefully manage the batch size.