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 <[email protected]> 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, [email protected] 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. > >
