Hi, I have two following cache both are loaded in memory (4 node cluster)and connected through affinity key.
1). Cache1 has 20 million records 2). Cache2 has 20 million records too. Now trying to fetch record through join query. But the execution time of query is ~160 sec. This is too high than expected ( expected is around a sec). Following is the query. Please let me know if I can improve performance. Query: SELECT p.*, cnt.ID as contID,cnt.Status as cntStatus, iib.count FROM Cache1 p join table(joinId VARCHAR(50) = ?) j on j.joinId = p.id JOIN (SELECT serialNumber, COUNT(*) AS count FROM Cache1 GROUP BY serialnumber) iib ON p.serialnumber = iib.serialnumber join Cache2 cnt on p.Affinitykey = cnt.Affinity key where lower(p.serialNumber) LIKE '%d2300%' and cnt.Status = 'Active' order by p.serialNumber DESC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY parameters are - [[D23500]] Note: I am indexing Cache1 on 'serialNumber' and Cache2 on 'Status' Thanks Tejas On Jan 5, 2017 5:01 PM, "Nikolai Tikhonov" <[email protected]> wrote: > Only make sure to use correct affinity key or properly configured affinity > function and AffinityKeyMapper. > > On Thu, Jan 5, 2017 at 2:26 PM, Tejashwa Kumar Verma < > [email protected]> wrote: > >> Thanks Nikolai, >> >> One more doubt . How can we verify that data has been collocated properly? >> >> -Tejas >> >> On Thu, Jan 5, 2017 at 4:51 PM, Nikolai Tikhonov <[email protected]> >> wrote: >> >>> Hi Kumar! >>> >>> 1) Collocated doesn't mean that query will be execting only one node. >>> 2) Yes, you got incorrect results. >>> >>> See the following page: http://apacheignite.grid >>> gain.org/docs/sql-queries#section-distributed-joins >>> >>> On Thu, Jan 5, 2017 at 2:05 PM, Tejashwa Kumar Verma < >>> [email protected]> wrote: >>> >>>> Hi , >>>> >>>> I am loading data by using affinity key. And ignite JDBC connection i >>>> am enabling collocated tp true. Now i have some following doubts-- >>>> >>>> 1).Will "Collocated=true" flag give assurance that query will get >>>> executed on same node, not in distributed mode(if data is not collocated )? >>>> 2). If data is not collocated and "Collocated" flag is true in Ignite >>>> JDBC connection then will i get correct response or not? >>>> >>>> >>>> Some reference form : http://apacheignite.gridgain.org/docs/jdbc-driver >>>> collocated : Flag that is used for optimization purposes. Whenever >>>> Ignite executes a distributed query, it sends sub-queries to individual >>>> cluster members. If you know in advance that the elements of your query >>>> selection are collocated together on the same node, Ignite can make >>>> significant performance and network optimizations. >>>> >>>> >>>> Thanks & regards >>>> Tejas >>>> >>>> >>>> >>>> On Thu, Dec 29, 2016 at 3:11 PM, Anil <[email protected]> wrote: >>>> >>>>> Hi Val, >>>>> >>>>> I did the same and seems join is not providing all the cache entries. >>>>> >>>>> Person cache -> AffinityKey(personId, equivalentid) >>>>> PersonDetail cache -> AffinityKey(detailId, equivalentid) >>>>> >>>>> both caches joined on equivalentId and collated is set to true in jdbc >>>>> url. Did I miss anything ? >>>>> >>>>> i see others also facing the similar issue [1] >>>>> >>>>> 1. http://apache-ignite-users.70518.x6.nabble.com/Re-Afinity >>>>> -Key-td9774.html#a9794 >>>>> >>>>> Thanks >>>>> >>>>> >>>>> >>>>> On 27 December 2016 at 23:39, vkulichenko < >>>>> [email protected]> wrote: >>>>> >>>>>> Anil, >>>>>> >>>>>> This will work. There is only one rule - everything with the same >>>>>> affinity >>>>>> key value will be mapped to the same partition, and therefore will >>>>>> reside on >>>>>> the same node. >>>>>> >>>>>> -Val >>>>>> >>>>>> >>>>>> >>>>>> -- >>>>>> View this message in context: http://apache-ignite-users.705 >>>>>> 18.x6.nabble.com/Affinity-tp9744p9757.html >>>>>> Sent from the Apache Ignite Users mailing list archive at Nabble.com. >>>>>> >>>>> >>>>> >>>> >>> >> >
