You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by "yann.blazart@externe.bnpparibas.com" <ya...@externe.bnpparibas.com> on 2019/05/21 17:38:17 UTC
Apache ignite rewrite badly SQL ???
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 co inner join TD dd
on dd.eid = co.eid
and dd.mid = co.mid inner 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.
Re: Apache ignite rewrite badly SQL ???
Posted by yann Blazart <ya...@gmail.com>.
Hello, I answer from another mail, but it's me :)
Well, using SqlQueryFieldEx I'm able to enforceJoinOrder.
Now on my Qualification env, rewriting some requests, it's good now.
Thanks for your help !
Now I have to find an algorithm now to optimize my requests following sql
requests ! :)
Le jeu. 23 mai 2019 à 10:49, Roman Kondakov <ko...@mail.ru> a écrit :
> 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.blazart@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 *fromTC co inner join TD dd
> on dd.eid = co.eid
> and dd.mid = co.mid inner 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=1WHERE ((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.
>
>
Re: Apache ignite rewrite badly SQL ???
Posted by Roman Kondakov <ko...@mail.ru>.
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.blazart@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.
>
Re: Apache ignite rewrite badly SQL ???
Posted by Ilya Kasnacheev <il...@gmail.com>.
Hello!
I imagine that maybe datagrip will only fetch a single page and not proceed
further, hence the difference?
Can you add e.g. LIMIT 10; to this statement, see if it makes a difference?
Regards,
--
Ilya Kasnacheev
вт, 21 мая 2019 г. в 20:38, yann.blazart@externe.bnpparibas.com <
yann.blazart@externe.bnpparibas.com>:
> 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 co inner join TD dd
> on dd.eid = co.eid
> and dd.mid = co.mid inner 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.
>