Hi!
Is your data collocated? To perform fast joins in the distributed
systems tables should be collocated on the join keys [1] to avoid
network latency.
Also Ignite's SQL optimizer currently is not able to select the best
join order. If your query is slow you should choose the join order
manually using enforceJoinOrder flag [2].
[1] https://apacheignite.readme.io/docs/affinity-collocation
[2]
https://ignite.apache.org/releases/latest/javadoc/org/apache/ignite/cache/query/SqlFieldsQuery.html#setEnforceJoinOrder-boolean-
--
Kind Regards
Roman Kondakov
On 21.05.2019 20:38, yann.blaz...@externe.bnpparibas.com wrote:
Hello all , I have a big problem.
I have a lot of tabes in my ignite cluster and one request take 10
minutes executed as a SQLFieldQuery, but only 153ms from DataGrip in
jdbc.
I explain, I have to change some names in request to not divulgate
informations.
I have 3 tables :
TC with 557 000 lines
TD with 3753 lines
TS with 1500 lines.
I want to execute this request :
select * from TC coinner join TD dd
on dd.eid = co.eid
and dd.mid = co.midinner join TS sch on sch.eid = dd.eid and sch.mid =
dd.mid;
On datagrip 153ms.
When I look into logs, I see that request has been rewrited like that :
SELECT *
FROM TC CO__Z0
/*CONTRACT.__SCAN_ */ INNER JOIN TD DD__Z1
/* batched:unicast TD_3: EID = CO__Z0.EID AND MID = CO__Z0.MID */ ON 1=1 /* WHERE (DD__Z1.EID = CO__Z0.EID) AND (DD__Z1.MID = CO__Z0.MID) */
INNER JOIN TS SCH__Z2
/* batched:unicast TS_2: EID = DD__Z1.EID AND MID = DD__Z1.MID */ ON 1=1
WHERE ((SCH__Z2.EID = DD__Z1.EID)
AND (SCH__Z2.MID = DD__Z1.MID))
AND ((DD__Z1.EID = CO__Z0.EID)
AND (DD__Z1.MID = CO__Z0.MID))
Why these (On 1=1 ) ???
If I understand, this make a scalar product of my lines that explain
the 10 minutes ! Why this is rewrited like that ???
Thanks for your help, regards
This message and any attachments (the "message") is
intended solely for the intended addressees and is confidential.
If you receive this message in error,or are not the intended
recipient(s),
please delete it and any copies from your systems and immediately notify
the sender. Any unauthorized view, use that does not comply with its
purpose,
dissemination or disclosure, either whole or partial, is prohibited.
Since the internet
cannot guarantee the integrity of this message which may not be
reliable, BNP PARIBAS
(and its subsidiaries) shall not be liable for the message if
modified, changed or falsified.
Do not print this message unless it is necessary, consider the
environment.
----------------------------------------------------------------------------------------------------------------------------------
Ce message et toutes les pieces jointes (ci-apres le "message")
sont etablis a l'intention exclusive de ses destinataires et sont
confidentiels.
Si vous recevez ce message par erreur ou s'il ne vous est pas destine,
merci de le detruire ainsi que toute copie de votre systeme et d'en
avertir
immediatement l'expediteur. Toute lecture non autorisee, toute
utilisation de
ce message qui n'est pas conforme a sa destination, toute diffusion ou
toute
publication, totale ou partielle, est interdite. L'Internet ne
permettant pas d'assurer
l'integrite de ce message electronique susceptible d'alteration, BNP
Paribas
(et ses filiales) decline(nt) toute responsabilite au titre de ce
message dans l'hypothese
ou il aurait ete modifie, deforme ou falsifie.
N'imprimez ce message que si necessaire, pensez a l'environnement.
