Hai Imad, On Feb 20, 2014, at 10:16 , imad hajj chahine <imad.hajj.chahine@gmail.com> wrote:

Hi Ying,

Thank you for your update, i will try to have an instance of monetdb on linux machine and check the difference. Can you please specify the optimal machine configuration for deploy (since we are using key value store model, target number of row per table > 1 billion): CPU,RAM,SSD.

This largely depends on the size of your hot data set, the type of queries, and the query load. It's important to have sufficient RAM to hold the how hot dataset. SSDs are always nice to have, but probably doesn't qualify the costs if you can have sufficient RAM.

Also do running monetdb in cluster will help to optimize queries response or its just for fail-over purpose? does monetdb support physical table data-partitioning? any reference on how monetdb operates in cluster mode, like data partitioning, query plans, merging results,...

For this, you probably want to have a look at the possibilities to query "REMOTE DATABASES" and creating "MULTIPLEX-FUNNELS" here:http://www.monetdb.org/Documentation/monetdbd-man-page With kind regards, Jennie

Thank you

...

...

...

On 17/02/14 11:44, imad hajj chahine wrote:

Hi,

I have the following query that do a pivot for the data stored in different tables, the time of the query is acceptable as long as the number of table to join is up to 10, 10 to 15 the query take more than 90 seconds to return, above 15 it does not return. Is there something i am missing, or i have to slice my query in smaller joins and the join back the results?

PS: the max number of records in each table is < 200k

select tval1.value as countall,tval2.value as ana1,tval3.value as ana2,tval4.value as count5,tval5.value as min5,tval6.value as max5,tval7.value as sum5,tval8.value as avg5,tval9.value as count6,tval10.value as min6,tval11.value as max6,tval12.value as sum6,tval13.value as avg6,tval14.value as count7,tval15.value as min7,tval16.value as max7,tval17.value as sum7,tval18.value as avg7 from "RPME".t_entity_cache tec join "RPME".t_value_int_cache tval1 on tec.id_schema=tval1.id_schema and tec.id <http://tec.id> <http://tec.id> <http://tec.id>=tval1.id_____entity and tval1.id_attribute=1 join "RPME".t_value_date_cache tval2 on tec.id_schema=tval2.id_schema and tec.id <http://tec.id> <http://tec.id> <http://tec.id>=tval2.id_____entity and tval2.id_attribute=2 join "RPME".t_value_string_cache tval3 on tec.id_schema=tval3.id_schema and tec.id <http://tec.id> <http://tec.id> <http://tec.id>=tval3.id_____entity and tval3.id_attribute=3 join "RPME".t_value_int_cache tval4 on tec.id_schema=tval4.id_schema and tec.id <http://tec.id> <http://tec.id> <http://tec.id>=tval4.id_____entity and tval4.id_attribute=4 join "RPME".t_value_numeric_cache tval5 on tec.id_schema=tval5.id_schema and tec.id <http://tec.id> <http://tec.id> <http://tec.id>=tval5.id_____entity and tval5.id_attribute=5 join "RPME".t_value_numeric_cache tval6 on tec.id_schema=tval6.id_schema and tec.id <http://tec.id> <http://tec.id> <http://tec.id>=tval6.id_____entity and tval6.id_attribute=6 join "RPME".t_value_numeric_cache tval7 on tec.id_schema=tval7.id_schema and tec.id <http://tec.id> <http://tec.id> <http://tec.id>=tval7.id_____entity and tval7.id_attribute=7 join "RPME".t_value_numeric_cache tval8 on tec.id_schema=tval8.id_schema and tec.id <http://tec.id> <http://tec.id> <http://tec.id>=tval8.id_____entity and tval8.id_attribute=8 join "RPME".t_value_int_cache tval9 on tec.id_schema=tval9.id_schema and tec.id <http://tec.id> <http://tec.id> <http://tec.id>=tval9.id_____entity and tval9.id_attribute=9 join "RPME".t_value_numeric_cache tval10 on tec.id_schema=tval10.id_schema and tec.id <http://tec.id> <http://tec.id> <http://tec.id>=tval10.id_____entity and tval10.id_attribute=10 join "RPME".t_value_numeric_cache tval11 on tec.id_schema=tval11.id_schema and tec.id <http://tec.id> <http://tec.id> <http://tec.id>=tval11.id_____entity and tval11.id_attribute=11 join "RPME".t_value_numeric_cache tval12 on tec.id_schema=tval12.id_schema and tec.id <http://tec.id> <http://tec.id> <http://tec.id>=tval12.id_____entity and tval12.id_attribute=12 join "RPME".t_value_numeric_cache tval13 on tec.id_schema=tval13.id_schema and tec.id <http://tec.id> <http://tec.id> <http://tec.id>=tval13.id_____entity and tval13.id_attribute=13 join "RPME".t_value_int_cache tval14 on tec.id_schema=tval14.id_schema and tec.id <http://tec.id> <http://tec.id> <http://tec.id>=tval14.id_____entity and tval14.id_attribute=14 join "RPME".t_value_numeric_cache tval15 on tec.id_schema=tval15.id_schema and tec.id <http://tec.id> <http://tec.id> <http://tec.id>=tval15.id_____entity and tval15.id_attribute=15 join "RPME".t_value_numeric_cache tval16 on tec.id_schema=tval16.id_schema and tec.id <http://tec.id> <http://tec.id> <http://tec.id>=tval16.id_____entity and tval16.id_attribute=16 join "RPME".t_value_numeric_cache tval17 on tec.id_schema=tval17.id_schema and tec.id <http://tec.id> <http://tec.id> <http://tec.id>=tval17.id_____entity and tval17.id_attribute=17 join "RPME".t_value_numeric_cache tval18 on tec.id_schema=tval18.id_schema and tec.id <http://tec.id> <http://tec.id> <http://tec.id>=tval18.id_____entity and tval18.id_attribute=18 where tec.id_schema=3

___________________________________________________ users-list mailing list users-list@monetdb.org <mailto:users-list@monetdb.org> <mailto:users-list@monetdb.org <mailto:users-list@monetdb.org>__> https://www.monetdb.org/____mailman/listinfo/users-list <https://www.monetdb.org/__mailman/listinfo/users-list> <https://www.monetdb.org/__mailman/listinfo/users-list <https://www.monetdb.org/mailman/listinfo/users-list>>