MonetDB insert bulk data best practice

1
votes

What is the best practice for inserting data via INSERT statements?

In MonetDB exists operation for bulk data loading (COPY INTO) but it requires file or stream and this solution doesn't suit my project.

I tried to use START TRANSACTION; ... COMMIT; but my best result was 220 000 records in 1 minute.

I use MonetDB in my .net project via ODBC connection.

How can I insert data via INSERT the fastest way?

2
.netodbcmonetdb

2 Answers

0
votes

You could try multiple (say, 1000) values per INSERT:

START TRANSACTION;
CREATE TABLE foo (a INTEGER, b STRING);
INSERT INTO foo VALUES (1, 'a'), (2, 'b'), (3, 'c');
COMMIT;

But really, bulk load with COPY INTO is generally a much better idea...

-1
votes

https://www.nuget.org/packages/MonetDb.Mapi

int count;
string tableName; // "\"MyTable\""
string columns; // '"' + string.Join("\", \"", columnArray) + '"'

dbCommand.Execute($"COPY {count} RECORDS INTO {tableName} FROM STDIN ({columns}) DELIMITERS ',','\\n','\\'';");

string records; // string.Join('\n', recArrayOfArray.Select(x => string.Join("," x))
dbCommand.Execute(records);