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 <sjoerd@acm.org> 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! DimitrisRead 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