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@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.
- 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?
- 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@revotechs.com Skype: revotech.d.tikhonov
developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list
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@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@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@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.
- 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?
- 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@revotechs.com Skype: revotech.d.tikhonov
developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list
-- Sjoerd Mullender
,
developers-list mailing list developers-list@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@revotechs.com Skype: revotech.d.tikhonov
developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list
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@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@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@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.
- 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?
- 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@revotechs.com Skype: revotech.d.tikhonov
developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list
-- Sjoerd Mullender
,
developers-list mailing list developers-list@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@revotechs.com Skype: revotech.d.tikhonov
developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list
participants (3)
-
d.tikhonov＠revotechs.com -
Sjoerd Mullender -
Stefan Manegold