MonetDB via ODBC questions

Stefan Manegold Stefan.Manegold at cwi.nl
Thu Oct 8 12:12:35 CEST 2015


Hi,

if you can create SQL script like

START TRANSACTION;
INSERT INTO table VALUES (1, 2, 3);
... -- n-1 more insert statements
COMMIT;

you probably can also generate a SQL script like

COPY n RECORDS INTO table FROM stdin USING DELIMITERS ',','\n';
1,2,3
... -- n-1 more data lines

That's hence bulk-loading instead of individual insert statements.

The individual insert statements incur overhead for each of them 
in the entire software stack which occurs only one with the single
bulkload statement.

Best,
Stefan


----- On Oct 8, 2015, at 11:54 AM, d tikhonov d.tikhonov at revotechs.com wrote:

> Thanks for fast answer.
> I understand how to solve my second question.
> But for create insert statements I used this article
> 
> 
> 
> https://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/LoadingBulkData
> 
> 
> "... A much better way to do lots of inserts is to make sure the inserts are
> bundled into a single transaction. This can easily be done by surrounding the
> inserts with a START TRANSACTION and COMMIT, as in:
> 
> START TRANSACTION;
> INSERT INTO table VALUES (1, 2, 3);
> ...
> COMMIT; ..."
> 
> Is it normal that 200 000 records insert in ~1 minute?
> 
> Can I somehow increase insert speed: disable logs or something else?
> 08.10.2015, 12:24, "Sjoerd Mullender" <sjoerd at monetdb.org>:
> 
> 
> 
> 
> See [1] for information about the different ways of getting data into
> MonetDB.
> See [2] for information about concurrency control. Especially the
> second section.
> 
> [1]
> https://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/LoadingBulkData
> [2] https://www.monetdb.org/blog/monetdb-sql-transaction-management-scheme
> 
> On 08/10/15 11:00, d.tikhonov at revotechs.com wrote:
> 
> 
> 
> Good day.
> 
> I use MonetDB in my .net project via odbc driver. And I have some
> questions, maybe you can help me.
> 
> 1. 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.
> 
> How can I insert data via INSERT the fastest way?
> 
> 2. Does monetDB support multi thread insert, update, delete via odbc?
> When I tried to test it I have got ODBCException: general exception.
> 
> How does MonetDB handles concurrent requests?
> 
> Thanks!
> 
> --
> 
> 
> Best Regards,
> Daniel Tikhonov
> 
> Programmer | Revotechs, LLC
> Revotechs.com
> Mobile: +375 (33) 341-87-54
> Email: d.tikhonov at revotechs.com
> Skype: revotech.d.tikhonov
> 
> 
> 
> _______________________________________________
> developers-list mailing list
> developers-list at monetdb.org
> https://www.monetdb.org/mailman/listinfo/developers-list
> 
> 
> 
> 
> 
> --
> Sjoerd Mullender
> 
> 
> ,
> 
> _______________________________________________
> developers-list mailing list
> developers-list at monetdb.org
> https://www.monetdb.org/mailman/listinfo/developers-list
> --
> Best Regards,
> Daniel Tikhonov
> Programmer | Revotechs, LLC
> Revotechs.com
> Mobile: +375 (33) 341-87-54
> Email: d.tikhonov at revotechs.com
> Skype: revotech.d.tikhonov
> 
> _______________________________________________
> developers-list mailing list
> developers-list at monetdb.org
> https://www.monetdb.org/mailman/listinfo/developers-list

-- 
| Stefan.Manegold at CWI.nl | DB Architectures   (DA) |
| www.CWI.nl/~manegold/  | Science Park 123 (L321) |
| +31 (0)20 592-4212     | 1098 XG Amsterdam  (NL) |


More information about the developers-list mailing list