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

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

Hi Dimitris (sorry for the typo),

@Bryan: Thanks for mentioning that, I was not aware of the windowing functionality :) I tried it out, and for me it seems to work. So for your simple example, you would do the following rewrite:

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

-->

SELECT MIN(o_id) FROM ( SELECT o_id FROM ( SELECT row_number() over (ORDER BY o_date DESC) as row, o_id FROM orders ) AS sub WHERE sub.row <= 3333 );

I had to add an extra subquery, since the WHERE clause in the part where you select the row_number(), is not aware of the identifier 'row' yet.

I would advise using this over the temporary tables, since a) it is much simpler to write and b) you don't have to worry about having to manage intermediate results yourself.

I hope you can extend this example to your original query.

Best, Robin

On 01-07-14 20:43, Bryan Senseman wrote:

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 mailto:users-list-bounces%2Bmonetdb=openbi.com@monetdb.org mailto: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 mailto: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
<mailto: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! 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 <mailto:users-list@monetdb.org>
https://www.monetdb.org/mailman/listinfo/users-list

_______________________________________________

users-list mailing list

users-list@monetdb.org  <mailto:users-list@monetdb.org>

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

---

users-list mailing list users-list@monetdb.org mailto: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.

---

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

Thanks Robin.

The problem with this is that the result I get is not correct. I don't know why but it is not equivalent to this: SELECT MIN(o_id) FROM (SELECT o_id FROM orders ORDER BY o_date DESC LIMIT 3333) AS s;

Here 's how managed to make it work (and seems to have better execution time):

CREATE LOCAL TEMPORARY TABLE latestorders AS SELECT o_id FROM orders ORDER BY o_date DESC LIMIT 3333 WITH DATA ON COMMIT PRESERVE ROWS;

DECLARE min_o_id int; SET min_o_id = (SELECT MIN(o_id) FROM latestorders);

SELECT i_id, i_title, a_fname, a_lname FROM item, author, order_line WHERE ol_o_id > min_o_id 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;

Embedding the query to the WHERE clause to avoid the intermediate variable doesn't work. I simply don't get any response (result or error) from the system.

On Wed, Jul 2, 2014 at 9:17 AM, Robin Cijvat < robin.cijvat@monetdbsolutions.com> wrote:

In my previous query, I forgot to give the biggest subquery an alias. So the query will become something like this:

SELECT MIN(o_id) FROM ( SELECT o_id FROM ( SELECT row_number() over (ORDER BY o_date DESC) as row, o_id FROM orders ) AS sub WHERE sub.row <= 3333 ) sub2;

On 02-07-14 09:15, Robin Cijvat wrote:

Hi Dimitris (sorry for the typo),

@Bryan: Thanks for mentioning that, I was not aware of the windowing functionality :) I tried it out, and for me it seems to work. So for your simple example, you would do the following rewrite:

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

-->

SELECT MIN(o_id) FROM ( SELECT o_id FROM ( SELECT row_number() over (ORDER BY o_date DESC) as row, o_id FROM orders ) AS sub WHERE sub.row <= 3333 );

I had to add an extra subquery, since the WHERE clause in the part where you select the row_number(), is not aware of the identifier 'row' yet.

I would advise using this over the temporary tables, since a) it is much simpler to write and b) you don't have to worry about having to manage intermediate results yourself.

I hope you can extend this example to your original query.

Best, Robin

On 01-07-14 20:43, Bryan Senseman wrote:

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

---

users-list mailing listusers-list@monetdb.orghttps://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

Hi,

The row_number() approach is slower. The inner query by itself (the one you posted above) takes around 1 sec. The whole temporary table creation and query execution that I showed before takes almost half of it so, I'd rather stick with that.

The only problem is that I can't use it as a subquery, I need to use an indermediate variable in between. Is there any way to "delete" the variable after I use it ? I know its lifetime is up to the end of user session but are there any other options ? I didn't see anything in the manual. If I won't find any workaround I'll declare a stored procedure.

Cheers, Dimitris

On Wed, Jul 2, 2014 at 11:03 PM, Robin Cijvat < robin.cijvat@monetdbsolutions.com> wrote:

Hi Dimitris,

Good to hear that you got it to work. I would not know why putting the aggregation in the where clause would cause this behavior. When you said that it seems to have a better execution time, did you mean the original temporary table approach vs the working temporary table approach or the temporary table approach vs the row_number() approach?

Cheers, Robin

---

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