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