Create stored procedure to add a list of ID's to a table
Hello, is it possible to create a stored procedure in MonetDB that will have one parameter containing a string with several thousands of comma-separated ID's and insert these comma-separated ID's as separate rows into a table? I am receiving thousands of ID's from a separate webservice and want to join these ID's to a table in monetDB, aggregate the results and return them back to the client for further processing in the GUI. I tried inserting the ID's into a temporary table using insert into instructions from PHP, but that didn't gave me the performance I wanted (about 6 seconds for 10.000 ID's). So the next approach would be to create a stored procedure receiving all the ID's in one parameter, splitting them and inserting them one-by-one into the temporary table... Or is there a better approach for this problem? thanks in advance, Richard
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 2013-02-05 01:24, Richard Siebeling wrote:
Hello,
is it possible to create a stored procedure in MonetDB that will have one parameter containing a string with several thousands of comma-separated ID's and insert these comma-separated ID's as separate rows into a table?
I am receiving thousands of ID's from a separate webservice and want to join these ID's to a table in monetDB, aggregate the results and return them back to the client for further processing in the GUI.
I tried inserting the ID's into a temporary table using insert into instructions from PHP, but that didn't gave me the performance I wanted (about 6 seconds for 10.000 ID's).
This is too slow. Are you absolutely sure you are doing this in a single database transaction? By default MonetDB uses autocommit which means a single transaction per query. This is not what you want since it slows down processing very significantly. See http://www.monetdb.org/Documentation/Cookbooks/SQLrecipies/LoadingBulkData for more information and possible solutions.
So the next approach would be to create a stored procedure receiving all the ID's in one parameter, splitting them and inserting them one-by-one into the temporary table...
Or is there a better approach for this problem?
thanks in advance,
Richard
_______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list
- -- Sjoerd Mullender -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.12 (GNU/Linux) Comment: Using GnuPG with undefined - http://www.enigmail.net/ iQCVAwUBURDDnT7g04AjvIQpAQJVkgP8C5gV1kIpQXDW0hCkucWq9Td0yGRqZk3s UZW5j+ZQ/TfG0yjzVVCXora71mSqycK9W7cryOnhfhEwGvS38qZOxx5LOAEGUSNz Tp/33Hm2KcuUi86AICkZF3tGtAWfQe6KNQ/0dEtVZueXm6I9odRZtrgtd1QckNVw DUpBACVQoBY= =FW8o -----END PGP SIGNATURE-----
participants (2)
-
Richard Siebeling -
Sjoerd Mullender