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