Re: Write performance problems with MySql and MariaDb

[Bogdan Degtyariov]

Hi Jerome,

The performance might be really poor if INSERT queries are inserting one row per statement like this:

INSERT INTO table (col1, col2,...) VALUES (1, 1, ...);
INSERT INTO table (col1, col2,...) VALUES (2, 2, ...);
....
INSERT INTO table (col1, col2,...) VALUES (N, N, ...);


Doing multiple rows per statement might boost the performance really well depending on the data being inserted:

INSERT INTO table (col1, col2,...) VALUES (1, 1, ...), (2, 2, ...), ... (N, N, ...);


Another way, which is much less efficient would be executing statements in batches of several queries. They will still have one row per INSERT, but several INSERT's in a batch:

INSERT INTO table (col1, col2,...) VALUES (1, 1, ...); INSERT INTO table (col1, col2,...) VALUES (2, 2, ...); .... INSERT INTO table (col1, col2,...) VALUES (N, N, ...);

If you do multiple statements in a batch make sure they are enabled through GUI option Details >> Connection >> [x] Allow multiple statements or through the connection string "MULTI_STATEMENTS=1"