Hi,
I'm afraid there is not much to optimize on the user side for a simple query like yours, other than having your data sorted by id,value .
Also, with only two columns involved --- with ~100 million records their sizes are ~400 MB and ~800 MB --- all data easily fits in the 32 GB of your machine.
To get more insight on what's going on, could you profile your query by prefixing it with "TRACE", i.e.,
TRACE SELECT id, count(distinct value) FROM table GROUP BY id;
and share the resulting performance trace?
Also could you share the following statistics:
select count(*) from table; select count(id) from table; select count(distinct id) from table; select count(value) from table; select count(distinct value) from table; select count(*) from ( select id,value from table group by id,value ) as x;
Best, Stefan
----- On Dec 14, 2017, at 10:59 AM, Alfio Bettini alfio.bettini@eidosmedia.com wrote:
Hi all,
we're testing MonetDB performances in order to understand if it could be possible to use it as data mart in our BI software.
We're considering a set of queries executed in our test environment (CentOS 7, 32 GB RAM, 4 cpu).
All the executed queries have quite good performances (between 10 and 45 seconds) except the following one that is taking several minutes in order to be executed:
SELECT id, count(distinct value)
FROM table
GROUP BY id;
The table contains 100 millions of data. It has about 130 columns (varchar, int, bigint, smallint, tinyint, timestamp, datetime). We have no BLOB/CLOB/Text types.
The fields used in the query have these data types:
id: int
value: bigint
Can you suggest us any optimizations in order to have this query run faster? Do you know some possible reasons why it's so slow?
Kind Regards,
Alfio Bettini
The information contained in this message, together with any attachments, may be privileged and confidential and is intended for the use of the addressee(s) only. Any review, copying, use or distribution of this email by others (including any of its attachments) is strictly prohibited. If you are not the intended recipient, please destroy this email and its attachments and contact the sender immediately. This email was sent by EidosMedia SpA
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list