Re: Left/Right joins performance - Need help!!
Hello Martin, Thanks for the useful tip. On cross checking the trace statements of both inner and left join queries, found out that most of the time taken by the left join query is spent at the following stage. This instance has happened for at least 20 times and each is around 40-60 seconds long while executing a single left/right join query. <<<-------- start quote - sample trace left join query --------->>> 45983604 | X_2138=<tmp_14607>[64851] := bat.append(X_2128=<tmp_14607>:bat[:oid,:str][64851],X_2137=<tmp_14544>:bat[:oid,:str][64851],true); 46443428 | X_3402=<tmp_6631>[64851] := bat.append(X_3390=<tmp_6631>:bat[:oid,:str][64851],X_3401=<tmp_20102>:bat[:oid,:str][64851],true); 53520133 | X_1820=<tmp_12102>[64851] := bat.append(X_1808=<tmp_12102>:bat[:oid,:str][64851],X_1819=<tmp_10437>:bat[:oid,:str][64851],true); 64750174 | X_1864=<tmp_12404>[64851] := bat.append(X_1854=<tmp_12404>:bat[:oid,:str][64851],X_1863=<tmp_21302>:bat[:oid,:str][64851],true); <<<-------- end quote - sample trace left join query --------->>> To my understanding, I think the discrepancy is because of the different approaches on deltas in executing both the queries. Not sure if I'm right. I would also like to know if this large left/inner join discrepancy is a default Monet behaviour or this is just because of some ignorant mistake/misconfigurations of mine (considering my heavy data load). Thanks & Regards, Vijayakrishna.P. Mobile : 9500402305.
Hi, https://www.monetdb.org/pipermail/users-list/2011-February/004659.html
* > One thing that we noticed with queries with joins is that inner join *>* seems *>* > to be significantly faster than left/right joins even if the results are *>* no *>* > different between the 3 types of joins.*
Found this in one of the archive mails in the mailing list. Has anyone else felt this problem?? To me, *inner join results come in a very few milliseconds* while *left join results take at least 5 mins* for the same set of tables and same number of matching rows. Is there anyone who has tried left join queries on Monet? Or am I facing this just because of some configuration issues?? Need of help badly. Thanks in advance. With Regards, Vijayakrishna.P. Mobile : 9500402305. On Thu, Oct 9, 2014 at 4:02 PM, Vijay Krishna <vijayakrishna55@gmail.com> wrote:
Hello Martin,
Thanks for the useful tip. On cross checking the trace statements of both inner and left join queries, found out that most of the time taken by the left join query is spent at the following stage. This instance has happened for at least 20 times and each is around 40-60 seconds long while executing a single left/right join query.
<<<-------- start quote - sample trace left join query --------->>>
45983604 | X_2138=<tmp_14607>[64851] := bat.append(X_2128=<tmp_14607>:bat[:oid,:str][64851],X_2137=<tmp_14544>:bat[:oid,:str][64851],true); 46443428 | X_3402=<tmp_6631>[64851] := bat.append(X_3390=<tmp_6631>:bat[:oid,:str][64851],X_3401=<tmp_20102>:bat[:oid,:str][64851],true); 53520133 | X_1820=<tmp_12102>[64851] := bat.append(X_1808=<tmp_12102>:bat[:oid,:str][64851],X_1819=<tmp_10437>:bat[:oid,:str][64851],true); 64750174 | X_1864=<tmp_12404>[64851] := bat.append(X_1854=<tmp_12404>:bat[:oid,:str][64851],X_1863=<tmp_21302>:bat[:oid,:str][64851],true);
<<<-------- end quote - sample trace left join query --------->>>
To my understanding, I think the discrepancy is because of the different approaches on deltas in executing both the queries. Not sure if I'm right.
I would also like to know if this large left/inner join discrepancy is a default Monet behaviour or this is just because of some ignorant mistake/misconfigurations of mine (considering my heavy data load).
Thanks & Regards,
Vijayakrishna.P. Mobile : 9500402305.
participants (1)
-
Vijay Krishna