Actually, I think you can trick it depending on your version of MonetDB and
use the RANK windowing function inside the innermost subquery and then use
a standard where clause on the appropriate outside snippet to limit the
returned rows.
Bryan
*From:* users-list [mailto:users-list-bounces+monetdb=openbi.com@monetdb.org]
*On Behalf Of *Dimitris Karampinas
*Sent:* Tuesday, July 01, 2014 2:27 PM
*To:* Communication channel for MonetDB users
*Subject:* 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
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 list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list -- ------------------------------ This transmission is confidential and intended solely for the use of the recipient named above. It may contain confidential, proprietary, or legally privileged information. If you are not the intended recipient, you are hereby notified that any unauthorized review, use, disclosure or distribution is strictly prohibited. If you have received this transmission in error, please contact the sender by reply e-mail and delete the original transmission and all copies from your system.