Re: MonetDB and cpu resources management

DimitrIs :) I thought of temporary tables before posting but a) I was skeptic about the extra overhead b) my first try to use them failed... For the aforementioned query I do the following: CREATE LOCAL TEMPORARY TABLE toporders AS SELECT o_id FROM orders ORDER BY o_date DESC LIMIT 3333 WITH DATA ON COMMIT PRESERVE ROWS; SELECT i_id, i_title, a_fname, a_lname FROM item, author, order_line WHERE ol_o_id > (SELECT MIN(o_id) FROM toporders) AND i_id = ol_i_id AND i_a_id = a_id AND i_subject = 'HOME' GROUP BY i_id, i_title, a_fname, a_lname ORDER BY SUM(ol_qty) DESC LIMIT 50; The command prompt returns but there is no result, neither any syntax/whatever error response. Cheers! Dimitris On Tue, Jul 1, 2014 at 5:29 PM, Robin Cijvat < robin.cijvat@monetdbsolutions.com> wrote:

Hi Dimitras.

MonetDB indeed does not support LIMIT in subqueries. A simple workaround is just storing the result of the subquery in a temporary table ( https://www.monetdb.org/Documentation/Manuals/SQLreference/Tables) and then referring to this temporary table in the main query.

Maybe not an ideal solution, but it will work.

Regards, Robin Cijvat

On 01-07-14 16:53, Dimitris Karampinas wrote:

Hi,

Thanks for your answers. I hope stethoscope will help.

One more question, not relevant to the previous one but I hope it's OK not to open a new thread.

I'm trying to run the BestSellers query from TPC-W on MonetDB. The query works as follows: - Take the smallest order id from the most recent 3333 orders. - Then take all order lines more recent than that, sum their quantities by item, and display the top 50 by total quantity.

Here is some SQL code for this:

SELECT i_id, i_title, a_fname, a_lname FROM item, author, order_line WHERE ol_o_id > (SELECT Min(o_id) FROM (SELECT o_id FROM orders ORDER BY o_date DESC LIMIT 3333) s) AND i_id = ol_i_id AND i_a_id = a_id AND i_subject = 'HOME' GROUP BY i_id, i_title, a_fname, a_lname ORDER BY Sum(ol_qty) DESC LIMIT 50;

I'm having trouble in expressing the nested sub-query:

SELECT MIN(o_id) FROM (SELECT o_id FROM orders ORDER BY o_date DESC LIMIT 3333) AS s;

Searching in previous mails in this list, I found out that MonetDB does not support ORDER BY or LIMIT in subqueries. Is there any workaround ?

Cheers! Dimitris

On Mon, Jun 30, 2014 at 2:24 PM, Sjoerd Mullender wrote:

...

-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1

On 2014-06-30 11:53, Dimitris Karampinas wrote:

...

Hi,

I'm trying to set the maximum number of cores using gdk_nr_threads parameter but it doesn't seem to work for me. I've tried "variants" of this command: monetdbd set gdk_nr_threads=4 start /my/dbfarm but it seems like gdk_nr_threads is not a supported property.

Additionally to this, I would like to ask how does MonetDB handle concurrent user sessions and the available cpu resources ? The manual says there is support for "inter- and intra- parallelism". Can someone roughly explain me what to expect or give me a pointer to the code that is responsible for resource management ?

Cheers! Dimitris

Read the manual: man monetdb. It will tell you about the nthreads=number command.

- -- Sjoerd Mullender -----BEGIN PGP SIGNATURE----- Version: GnuPG v1 Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/

iQCVAwUBU7FW4T7g04AjvIQpAQKNnQP/STLE1LROqd1bixEDOXRkFt2C8UtbPAMo WlohnW7z7xEis9EziIq/rEOV8n/xnnyhiZG6YENoL+faB3UbM2OIHCr7UgZqL3hH VlpsB0NIztt6XRT/Ax+MlwwYf9pOGefbDi6PlHzNkitBVfah/H6ws613oiAD1/lZ ElMs8AQ0m28= =iDi8 -----END PGP SIGNATURE----- _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

_______________________________________________ users-list mailing listusers-list@monetdb.orghttps://www.monetdb.org/mailman/listinfo/users-list

_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list