Hi Val, I have created a complex query and looks good in terms of query plan. But query execution time is very high.
query - SELECT P.serialnumber, iP.count, cnt.itemnumber, cnt.status, cnt.enddate FROM Product P left JOIN ( SELECT serialnumber, COUNT(*) AS count FROM Product GROUP BY serialnumber ) iP ON P.serialnumber = iP.serialnumber JOIN contracts cnt on P.equipmentid = cnt.equipmentid where P.serialnumber = 's3' and status = 'Active' explain plan - SELECT IB.SERIALNUMBER, IIB.COUNT, CNT.ITEMNUMBER, CNT.STATUS, CNT.ENDDATE FROM PUBLIC.PRODUCT P * /* PUBLIC.SERNO_INDEX: SERIALNUMBER = 's3' */* /* WHERE P.SERIALNUMBER = 's3' */ LEFT OUTER JOIN ( SELECT SERIALNUMBER, COUNT(*) AS COUNT FROM PUBLIC.PRODUCT GROUP BY SERIALNUMBER ) IP /* SELECT SERIALNUMBER, COUNT(*) AS COUNT FROM PUBLIC.PRODUCT * /++ PUBLIC.SERNO_INDEX: SERIALNUMBER IS ?1 ++/* WHERE SERIALNUMBER IS ?1 GROUP BY SERIALNUMBER * /++ group sorted ++/: SERIALNUMBER = P.SERIALNUMBER* */ ON P.SERIALNUMBER = IP.SERIALNUMBER INNER JOIN PUBLIC.CONTRACTS CNT * /* PUBLIC.EQ_INDEX: EQUIPMENTID = P.EQUIPMENTID */* ON P.EQUIPMENTID = CNT.EQUIPMENTID WHERE (P.SERIALNUMBER = 's3') AND (STATUS = 'Active') Contracts are related to Product. So i created product Id reference in Contract and annotated with @AffinityKeyMapped. I feel that affinity is not happening. Do you see any issues with above query or configuration ? Thanks. On 20 December 2016 at 02:11, vkulichenko <valentin.kuliche...@gmail.com> wrote: > I would also create indexes on 'id' fields at least. And do not forget to > check the execution plan [1] to make sure everything works as expected. > > [1] http://apacheignite.gridgain.org/docs/performance-and-debugging > > -Val > > > > -- > View this message in context: http://apache-ignite-users. > 70518.x6.nabble.com/Complex-queries-tp9626p9630.html > Sent from the Apache Ignite Users mailing list archive at Nabble.com. >