DuckDB Insert Benchmark

We want to compare three methods of inserting data to a DuckDB table:

Individual Inserts
Individual Inserts - Wrapped in a commit
Batched Value Inserts

For our test we are going to be running via JDBC driver as we want to test the DuckDB performance for QStudio. The numbers are so significantly different that the principle should apply to all DuckDB instances.

Benchmark Code

Individual Inserts

-- DROP TABLE IF EXISTS exportTbl;  CREATE TABLE exportTbl ( "sym" VARCHAR, "date" DATE, "open" DOUBLE, "high" DOUBLE, "low" DOUBLE, "close" DOUBLE, "volume" BIGINT, "pre" VARCHAR ); SELECT COUNT(*) FROM exportTbl;

BEGIN TRANSACTION;
INSERT INTO exportTbl VALUES('09II.L', '2022-10-24', 97.76, 97.76, 95.795, 95.795, 590590, '0');
INSERT INTO exportTbl VALUES('09II.L', '2022-10-25', 95.89, 96.96, 95.89, 96.96, 86342, '0');
INSERT INTO exportTbl VALUES('09II.L', '2022-10-26', 98.65, 98.65, 97.905, 97.905, 35489, '0');
..........
COMMIT;

Batched Inserts

BEGIN TRANSACTION;
INSERT INTO exportTbl VALUES('09II.L', '2022-10-24', 97.76, 97.76, 95.795, 95.795, 590590, '0')
,('09II.L', '2022-10-25', 95.89, 96.96, 95.89, 96.96, 86342, '0')
,('09II.L', '2022-10-26', 98.65, 98.65, 97.905, 97.905, 35489, '0')
,('09II.L', '2022-10-27', 99.7, 99.7, 98.04, 98.04, 64388, '0')
,('09II.L', '2022-10-28', 97.95, 98.61, 97.95, 98.61, 52399, '0')
,('09II.L', '2022-10-31', 101.05, 101.85, 101.05, 101.85, 104970, '0')
.......
,('0A00.L', '2021-03-22', 91.5343, 91.5343, 89.9867, 89.9867, 40764, '0');

COMMIT;

DuckDB Benchmark Results

Table shows the milliseconds taken for inserting the given number of rows into an in-memory table for each approach.

rows	DuckDB IndividualWithCommit (ms)	DuckDB IndividualInserts (ms)	DuckDB BatchInsert (ms)	H2 BatchInsert (ms)	ColumnarDB Individual (ms)
1000	400	500	70
2000	926	1130	114
4000	2340	2948	220	51
20000			1150	123	450
40000			2264	240	800

*Note H2 is a java embedded database. ColumnarDB is an enterprise column-oriented database.

Graph showing Insert Time-Taken. Smaller = Better

DuckDB Insert Methods Benchmark Results per Row

Observations

Batched Inserts are 10x faster, even on small data sets.
Batched inserts scale sub-linearly, Individual Inserts do not. i.e. The larger the number of inserts, it gets slower per insert.

Open Question, is there a size limit for batched inserts?
I tested 40,000 inserts which ran in 2264ms.

Should also try the appender.