Hello Jennie,

Thanks for responding. Here are the answers for the questions you had asked

1. you can prefix your queries with the TRACE keyword to see where the time went
I am attaching Trace output. 4.trace is TRACE from 4 threads ( --set "gdk_nr_threads=4" ), 1.trace files is TRACE from 1 threads monet server run ( --set "gdk_nr_threads=1"). I was expecting 4 threads run to be ~2X faster, but its slower than 1 thread run.

2. are the times from running the queries once, e.g. for the first time? have you tried to run the queries repeatedly to see if there are any changes in the execution time?
No, I am taking these snapshots after 3-4 times prior runs, so that the data is in memory, and no IO,

3. are there any other applications running on your windows machine?

No, apart from Windows, there is no other application running on the machine.

4. does your windows run in a VM or on physical hardware?

Its running on Physical hardware on my windows machine.

Please let me know if more info is required. In the meantime, I am trying to use tomograph utility.

Regards,

Mohit

On Tue, Mar 21, 2017 at 1:25 PM, Ying Zhang <Y.Zhang@cwi.nl> wrote:

Hai Manish,

For the use case you described here, using merge table won’t help the performance, because, as Martin mentioned, MonetDB would already use all cores for the single base table in your merge table.

However, the performance degradation is a bit surprising. Some following up questions:
1. you can prefix your queries with the TRACE keyword to see where the time went
2. are the times from running the queries once, e.g. for the first time? have you tried to run the queries repeatedly to see if there are any changes in the execution time?
3. are there any other applications running on your windows machine?
4. does your windows run in a VM or on physical hardware?

Regards,
Jennie

> On 19 Mar 2017, at 04:25, Manish gupta <gahoimnshg@gmail.com> wrote:
>
> Dear all,
> I am checking the performance of merge table vs single table on single machine with 4 cores. The fact table contains ~10million records. I am using # MonetDB 5 server v11.25.9 "Dec2016-SP2" on windows machine ( core i7 16GB physical ram ).
> I have created a merge table which contains fact table and 3 blank copies of fact table. I am seeing surprisingly slow performance of merge table queries compared to base table queries.
>
> Base table query
> =================
> sql>select sum(f_1_1_1), count(*), f_1_1_7,f_1_1_5 from t3760_279_1_1 group by f_1_1_7,f_1_1_5 order by sum(f_1_1_1) desc limit 10;
> +-----------+------+---------+---------+
> | L2 | L5 | f_1_1_7 | f_1_1_5 |
> +===========+======+=========+=========+
> | 125885351 | 14 | SALE | 8084869 |
> | 125629268 | 15 | SALE | 6852518 |
> | 121733416 | 16 | SALE | 5440605 |
> | 110328868 | 13 | SALE | 4842411 |
> | 100848236 | 11 | SALE | 8068509 |
> | 100719291 | 16 | SALE | 5822736 |
> | 93127875 | 11 | SALE | 6529612 |
> | 91034094 | 13 | SALE | 3401293 |
> | 90766181 | 10 | SALE | 8084900 |
> | 88848574 | 10 | SALE | 2660811 |
> +-----------+------+---------+---------+
> 10 tuples (44.2s)
>
> Merge Table Query
> =================
> sql>select sum(f_1_1_1), count(*), f_1_1_7,f_1_1_5 from mt279_1_1 group by f_1_1_7,f_1_1_5 order by sum(f_1_1_1) desc limit 10;
> +-----------+------+---------+---------+
> | L2 | L5 | f_1_1_7 | f_1_1_5 |
> +===========+======+=========+=========+
> | 125885351 | 14 | SALE | 8084869 |
> | 125629268 | 15 | SALE | 6852518 |
> | 121733416 | 16 | SALE | 5440605 |
> | 110328868 | 13 | SALE | 4842411 |
> | 100848236 | 11 | SALE | 8068509 |
> | 100719291 | 16 | SALE | 5822736 |
> | 93127875 | 11 | SALE | 6529612 |
> | 91034094 | 13 | SALE | 3401293 |
> | 90766181 | 10 | SALE | 8084900 |
> | 88848574 | 10 | SALE | 2660811 |
> +-----------+------+---------+---------+
> 10 tuples (1m 51s)
>
>
> For the same results ( because merge table is containing the base table and 3 blank copies of base table ), merge table query is taking more than 2.5 times the base table runtime. Why is it so?
>
> Thanks,
> Mohit
>
>

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