Optimizing Batch INSERT Statements

To quickly write data to a database, many developers use batch INSERT statements. DDM is compatible with the protocol and syntax of MySQL. The syntax of Batch INSERT is listed below:

INSERT [IGNORE] [INTO] table_name(column_name, ...) VALUES (value1, ...), (value2, ...), ...;

The batch insert performance depends on:

split_chunk_size

Degree of Parallelism (DOP)

Number of shards

- Number of columns

You can adjust the DOP, number of shards, and number of columns based on your actual needs, and these factors are often closely related to read performance. This section describes how to configure split_chunk_size.

Using a Hint to Change the Value of split_chunk_size (with 1000 As Its Default Value)

Syntax:

/*+split_chunk_size=2000*/<INSERT-VALUES-statements>

Description:

Set BATCH_INSERT_POLICY to SPLIT and test the performance when split_chunk_size is set to 2000 using the following example. The test result shows that the performance does not improve significantly when split_chunk_size exceeds 1000.

Example:

/*+split_chunk_size=2000*/insert into tbl(name) values("zhangsan"),("lisi")....;

BATCH_INSERT_POLICY can be set only in DDM 3.1.5.0 or later. Its default value is NONE. The default value of split_chunk_size is 1000.