Rajesh, can you please show your query here together with execution plan? D.
On Thu, Feb 1, 2018 at 8:36 AM, Rajesh Kishore <rajesh10si...@gmail.com> wrote: > Hi Andrey > Thanks for your response. > I am using native ignite persistence, saving data locally and as of now I > don't have distributed cache, having only one node. > > By looking at the doc, it does not look like affinity key is applicable > here. > > Pls suggest. > > Thanks Rajesh > > On 1 Feb 2018 6:27 p.m., "Andrey Mashenkov" <andrey.mashen...@gmail.com> > wrote: > >> Hi Rajesh, >> >> >> Possibly, you data is not collocated and subquery return less retults as >> it executes locally. >> Try to rewrite IN into JOIN and check if query with >> query#setDistributedJoins(true) will return expected result. >> >> It is recommended >> 1. replace IN with JOIN due to performance issues [1]. >> 2. use data collocation [2] if possible rather than turning on >> distributed joins. >> >> [1] https://apacheignite-sql.readme.io/docs/performance-and- >> debugging#section-sql-performance-and-usability-considerations >> [2] https://apacheignite.readme.io/docs/affinity-collocation >> #section-collocate-data-with-data >> >> On Thu, Feb 1, 2018 at 3:44 PM, Rajesh Kishore <rajesh10si...@gmail.com> >> wrote: >> >>> Hi All, >>> >>> As of now, we have less than 1 M records , and attribute split into >>> few(3) tables >>> with index created. >>> We are using combination of join & IN clause(sub query) in the SQL >>> query , for some reason this query does not return any response. >>> But, the moment we remove the IN clause and use just the join, the query >>> returns the result. >>> Note that as per EXPLAIN PLAN , the sub query also seems to be using the >>> defined >>> indexes. >>> >>> What are the recommendations for using such queries , are there any >>> guidelines, What we are doing wrong here? >>> >>> Thanks, >>> Rajesh >>> >>> >>> >>> >>> >>> >>> >> >> >> -- >> Best regards, >> Andrey V. Mashenkov >> >
