MonetDB via ODBC questions

Sjoerd Mullender sjoerd at monetdb.org
Thu Oct 8 12:56:00 CEST 2015


On 08/10/15 12:12, Stefan Manegold wrote:
> 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.

In addition, as explained in the page I referenced, if you use INSERT,
only a single core is used, if you use COPY INTO, multiple cores are
involved.

> 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
> 


-- 
Sjoerd Mullender

-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 473 bytes
Desc: OpenPGP digital signature
URL: <http://www.monetdb.org/pipermail/developers-list/attachments/20151008/598d256b/attachment.sig>


More information about the developers-list mailing list