You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by Anil <an...@gmail.com> on 2016/12/27 11:20:47 UTC
Affinity
Hi,
is it mandatory to use key of a cache as part of the affinity key of
another cache ?
i see all examples on github are using the same.
In my scenario, key of a cache (person) cannot be part of affinity key of
another cache (person details). how can we achieve affinity?
Thanks.
Re: Affinity
Posted by vkulichenko <va...@gmail.com>.
Hi Tejas,
I don't see anything obvious in the plan. Note that when you join, you still
have to scan one of the sides, so if intermediate datasets after applying
conditions are still large, performance can be not very good. Joins are
applied in the order they appear in the plan, so you can go through the
execution flow and find out where is the bottleneck.
-Val
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Affinity-tp9744p10013.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: Affinity
Posted by Tejashwa Kumar Verma <te...@gmail.com>.
Hi Val,
Did you got the chance to have a look of explain plan?
Please provide your comments. It will be great help thanks
-Tejas
On Mon, Jan 9, 2017 at 11:55 AM, Tejashwa Kumar Verma <
tejashwa.verma@gmail.com> wrote:
> Hi Val,
>
> Please have a look in execution plan of the query and please suggest me if
> you see any improvement.
>
>
> SELECT
> P._KEY AS __C0,
> P._VAL AS __C1,
> P.EQUIPMENTID AS __C2,
> P.INSTALLBASEID AS __C3,
> P.MATERIALID AS __C4,
> P.OVERLENGTHMATERIALID AS __C5,
> P.DEVICENAME AS __C6,
> P.MODULE AS __C7,
> P.SUBMODULE AS __C8,
> P.SUBSUBMODULE AS __C9,
> P.SUBSUBSUBMODULE AS __C10,
> P.PARTNUMBER AS __C11,
> P.REVISION AS __C12,
> P.MODELNUMBER AS __C13,
> P.SERIALNUMBER AS __C14,
> P.DESCRIPTION AS __C15,
> P.SOFTWARERELEASE AS __C16,
> P.MATERIALSTATUS AS __C17,
> P.ROHS2DEPSITION AS __C18,
> P.LASTORDERTIME AS __C19,
> P.EOSTIME AS __C20,
> P.EOSFLAG AS __C21,
> P.ITEMGROUPDESCRIPTION AS __C22,
> P.PRODUCTCATEGORY AS __C23,
> P.PRODUCTFAMILY AS __C24,
> P.PRODUCTLINE AS __C25,
> P.PRODUCTNAME AS __C26,
> P.ISPARENTIBASE AS __C27,
> P.SERVICEDECLINE AS __C28,
> P.SERVICEELIGIBLE AS __C29,
> P.SERVICEOPTIONAL AS __C30,
> P.SHIPQTR AS __C31,
> P.SHIPTIME AS __C32,
> P.DEADWARRANTYTIME AS __C33,
> P.SOFTWAREWARRANTYTIME AS __C34,
> P.WARRANTYSTARTTIME AS __C35,
> P.WARRANTYENDTIME AS __C36,
> P.WARRANTYTYPE AS __C37,
> P.DTWARRANTYTYPE AS __C38,
> P.REGISTRATIONTIME AS __C39,
> P.MATERIALGROUP AS __C40,
> P.MATERIALTYPE AS __C41,
> P.COMPONENTNUMBER AS __C42,
> P.TOPCOMPONENTIBASE AS __C43,
> P.INSTALLEDATTHEATER AS __C44,
> P.INSTALLEDAT AS __C45,
> P.INSTALLEDATBPID AS __C46,
> P.MANAGEDRESLLERBPID AS __C47,
> P.INSTALLEDTIME AS __C48,
> P.SERVICEDECLINEREASON AS __C49,
> P.ASSEMBLYNUMBER AS __C50,
> P.ASSEMBLYREV AS __C51,
> P.ASSEMPLYHWREV AS __C52,
> P.RMANUMBER AS __C53,
> P.CUSTOMERPONUMBER AS __C54,
> P.SALESORDERNUMBER AS __C55,
> P.SALESORDERLINENUMBER AS __C56,
> P.POSDISTRIBUTOR AS __C57,
> P.RESELLER AS __C58,
> P.ENDUSERPARENTNAME AS __C59,
> P.ENDUSERBPID AS __C60,
> P.ENDUSERNAME AS __C61,
> P.ENDUSERPARENTBPID AS __C62,
> P.INSTALLEDATADDRESSLINE AS __C63,
> P.INSTALLEDATCITY AS __C64,
> P.INSTALLEDATCOUNTRY AS __C65,
> P.INSTALLEDATSTATE AS __C66,
> P.TYPE AS __C67,
> P.FPCLCC AS __C68,
> P.PARENTSERIALNUMBER AS __C69,
> P.PARENTCHASSISTYPE AS __C70,
> P.PARENTCHASSISNAME AS __C71,
> P.PARENTCHASSISRELEASE AS __C72,
> P.JUNOS AS __C73,
> P.HASFRU AS __C74,
> P.HASINVENTORY AS __C75,
> P.HASVERSION AS __C76,
> P.HASFPC AS __C77,
> P.SOURCE AS __C78,
> P.REMARKS AS __C79,
> P.DELETED AS __C80,
> P.HASCHILDS AS __C81,
> P.CONTRACTID AS __C82,
> P.CONTRACTSTATUS AS __C83,
> P.CONTRACTSTARTTIME AS __C84,
> P.CONTRACTENDTIME AS __C85,
> P.SUPPORTCOVERAGETYPE AS __C86,
> P.COVERAGETYPE AS __C87,
> P.PRODDESCRIPTION AS __C88,
> P.DUAL AS __C89,
> P.INSTALLEDATACCID AS __C90,
> P.MANAGEDRESLLERACCID AS __C91,
> P.ENDUSERACCID AS __C92,
> CNT.CONTRACTID AS __C93,
> CNT.STARTDATE AS __C94,
> CNT.ENDDATE AS __C95,
> CNT.SERVICESKU AS __C96,
> CNT.CONTRACTSTATUS AS __C97,
> IIB.COUNT AS __C98
> FROM CACHE2.CONTRACT CNT
> /* CACHE2."contractStatus_idx": CONTRACTSTATUS = 'Active' */
> /* WHERE CNT.CONTRACTSTATUS = 'Active'
> */
> INNER JOIN TABLE(JOINID VARCHAR(50)=?1) J
> /* function */
> ON 1=1
> INNER JOIN CACHE1.INSTALLBASE P
> /* CACHE1."installedAtBpid_idx": INSTALLEDATBPID = J.JOINID */
> ON 1=1
> /* WHERE (J.JOINID = P.INSTALLEDATBPID)
> AND ((P.EQUIPMENTID = CNT.EQUIPMENTID)
> AND (LOWER(P.SERIALNUMBER) LIKE '%d2300%'))
> */
> INNER JOIN (
> SELECT
> SERIALNUMBER,
> COUNT(*) AS COUNT
> FROM CACHE1.INSTALLBASE
> GROUP BY SERIALNUMBER
> ) IIB
> /* SELECT
> SERIALNUMBER,
> COUNT(*) AS COUNT
> FROM CACHE1.INSTALLBASE
> /++ CACHE1."serialNumber_idx": SERIALNUMBER IS ?3 ++/
> WHERE SERIALNUMBER IS ?3
> GROUP BY SERIALNUMBER
> /++ group sorted ++/: SERIALNUMBER = P.SERIALNUMBER
> */
> ON 1=1
> WHERE ((CNT.CONTRACTSTATUS = 'Active')
> AND (LOWER(P.SERIALNUMBER) LIKE '%d2300%'))
> AND ((P.EQUIPMENTID = CNT.EQUIPMENTID)
> AND ((P.SERIALNUMBER = IIB.SERIALNUMBER)
> AND (J.JOINID = P.INSTALLEDATBPID)))
> ORDER BY 15 DESC
> LIMIT 10
> Count - SELECT
> __C0 AS _KEY,
> __C1 AS _VAL,
> __C2 AS EQUIPMENTID,
> __C3 AS INSTALLBASEID,
> __C4 AS MATERIALID,
> __C5 AS OVERLENGTHMATERIALID,
> __C6 AS DEVICENAME,
> __C7 AS MODULE,
> __C8 AS SUBMODULE,
> __C9 AS SUBSUBMODULE,
> __C10 AS SUBSUBSUBMODULE,
> __C11 AS PARTNUMBER,
> __C12 AS REVISION,
> __C13 AS MODELNUMBER,
> __C14 AS SERIALNUMBER,
> __C15 AS DESCRIPTION,
> __C16 AS SOFTWARERELEASE,
> __C17 AS MATERIALSTATUS,
> __C18 AS ROHS2DEPSITION,
> __C19 AS LASTORDERTIME,
> __C20 AS EOSTIME,
> __C21 AS EOSFLAG,
> __C22 AS ITEMGROUPDESCRIPTION,
> __C23 AS PRODUCTCATEGORY,
> __C24 AS PRODUCTFAMILY,
> __C25 AS PRODUCTLINE,
> __C26 AS PRODUCTNAME,
> __C27 AS ISPARENTIBASE,
> __C28 AS SERVICEDECLINE,
> __C29 AS SERVICEELIGIBLE,
> __C30 AS SERVICEOPTIONAL,
> __C31 AS SHIPQTR,
> __C32 AS SHIPTIME,
> __C33 AS DEADWARRANTYTIME,
> __C34 AS SOFTWAREWARRANTYTIME,
> __C35 AS WARRANTYSTARTTIME,
> __C36 AS WARRANTYENDTIME,
> __C37 AS WARRANTYTYPE,
> __C38 AS DTWARRANTYTYPE,
> __C39 AS REGISTRATIONTIME,
> __C40 AS MATERIALGROUP,
> __C41 AS MATERIALTYPE,
> __C42 AS COMPONENTNUMBER,
> __C43 AS TOPCOMPONENTIBASE,
> __C44 AS INSTALLEDATTHEATER,
> __C45 AS INSTALLEDAT,
> __C46 AS INSTALLEDATBPID,
> __C47 AS MANAGEDRESLLERBPID,
> __C48 AS INSTALLEDTIME,
> __C49 AS SERVICEDECLINEREASON,
> __C50 AS ASSEMBLYNUMBER,
> __C51 AS ASSEMBLYREV,
> __C52 AS ASSEMPLYHWREV,
> __C53 AS RMANUMBER,
> __C54 AS CUSTOMERPONUMBER,
> __C55 AS SALESORDERNUMBER,
> __C56 AS SALESORDERLINENUMBER,
> __C57 AS POSDISTRIBUTOR,
> __C58 AS RESELLER,
> __C59 AS ENDUSERPARENTNAME,
> __C60 AS ENDUSERBPID,
> __C61 AS ENDUSERNAME,
> __C62 AS ENDUSERPARENTBPID,
> __C63 AS INSTALLEDATADDRESSLINE,
> __C64 AS INSTALLEDATCITY,
> __C65 AS INSTALLEDATCOUNTRY,
> __C66 AS INSTALLEDATSTATE,
> __C67 AS TYPE,
> __C68 AS FPCLCC,
> __C69 AS PARENTSERIALNUMBER,
> __C70 AS PARENTCHASSISTYPE,
> __C71 AS PARENTCHASSISNAME,
> __C72 AS PARENTCHASSISRELEASE,
> __C73 AS JUNOS,
> __C74 AS HASFRU,
> __C75 AS HASINVENTORY,
> __C76 AS HASVERSION,
> __C77 AS HASFPC,
> __C78 AS SOURCE,
> __C79 AS REMARKS,
> __C80 AS DELETED,
> __C81 AS HASCHILDS,
> __C82 AS CONTRACTID,
> __C83 AS CONTRACTSTATUS,
> __C84 AS CONTRACTSTARTTIME,
> __C85 AS CONTRACTENDTIME,
> __C86 AS SUPPORTCOVERAGETYPE,
> __C87 AS COVERAGETYPE,
> __C88 AS PRODDESCRIPTION,
> __C89 AS DUAL,
> __C90 AS INSTALLEDATACCID,
> __C91 AS MANAGEDRESLLERACCID,
> __C92 AS ENDUSERACCID,
> __C93 AS CONTID,
> __C94 AS CONTSTARTDATE,
> __C95 AS CONTENDDATE,
> __C96 AS CONTSERVICESKU,
> __C97 AS CONTSTATUS,
> __C98 AS COUNT
> FROM PUBLIC.__T0
> /* CACHE1."merge_scan" */
> ORDER BY 15 DESC
> LIMIT 10 OFFSET 0
>
>
>
> -Tejas
>
> On Sat, Jan 7, 2017 at 1:07 AM, vkulichenko <valentin.kulichenko@gmail.com
> > wrote:
>
>> Hi Tejas,
>>
>> Did you check the execution plan? Are there any scans?
>>
>> -Val
>>
>>
>>
>> --
>> View this message in context: http://apache-ignite-users.705
>> 18.x6.nabble.com/Affinity-tp9744p9943.html
>> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>>
>
>
Re: Affinity
Posted by Tejashwa Kumar Verma <te...@gmail.com>.
Hi Val,
Please have a look in execution plan of the query and please suggest me if
you see any improvement.
SELECT
P._KEY AS __C0,
P._VAL AS __C1,
P.EQUIPMENTID AS __C2,
P.INSTALLBASEID AS __C3,
P.MATERIALID AS __C4,
P.OVERLENGTHMATERIALID AS __C5,
P.DEVICENAME AS __C6,
P.MODULE AS __C7,
P.SUBMODULE AS __C8,
P.SUBSUBMODULE AS __C9,
P.SUBSUBSUBMODULE AS __C10,
P.PARTNUMBER AS __C11,
P.REVISION AS __C12,
P.MODELNUMBER AS __C13,
P.SERIALNUMBER AS __C14,
P.DESCRIPTION AS __C15,
P.SOFTWARERELEASE AS __C16,
P.MATERIALSTATUS AS __C17,
P.ROHS2DEPSITION AS __C18,
P.LASTORDERTIME AS __C19,
P.EOSTIME AS __C20,
P.EOSFLAG AS __C21,
P.ITEMGROUPDESCRIPTION AS __C22,
P.PRODUCTCATEGORY AS __C23,
P.PRODUCTFAMILY AS __C24,
P.PRODUCTLINE AS __C25,
P.PRODUCTNAME AS __C26,
P.ISPARENTIBASE AS __C27,
P.SERVICEDECLINE AS __C28,
P.SERVICEELIGIBLE AS __C29,
P.SERVICEOPTIONAL AS __C30,
P.SHIPQTR AS __C31,
P.SHIPTIME AS __C32,
P.DEADWARRANTYTIME AS __C33,
P.SOFTWAREWARRANTYTIME AS __C34,
P.WARRANTYSTARTTIME AS __C35,
P.WARRANTYENDTIME AS __C36,
P.WARRANTYTYPE AS __C37,
P.DTWARRANTYTYPE AS __C38,
P.REGISTRATIONTIME AS __C39,
P.MATERIALGROUP AS __C40,
P.MATERIALTYPE AS __C41,
P.COMPONENTNUMBER AS __C42,
P.TOPCOMPONENTIBASE AS __C43,
P.INSTALLEDATTHEATER AS __C44,
P.INSTALLEDAT AS __C45,
P.INSTALLEDATBPID AS __C46,
P.MANAGEDRESLLERBPID AS __C47,
P.INSTALLEDTIME AS __C48,
P.SERVICEDECLINEREASON AS __C49,
P.ASSEMBLYNUMBER AS __C50,
P.ASSEMBLYREV AS __C51,
P.ASSEMPLYHWREV AS __C52,
P.RMANUMBER AS __C53,
P.CUSTOMERPONUMBER AS __C54,
P.SALESORDERNUMBER AS __C55,
P.SALESORDERLINENUMBER AS __C56,
P.POSDISTRIBUTOR AS __C57,
P.RESELLER AS __C58,
P.ENDUSERPARENTNAME AS __C59,
P.ENDUSERBPID AS __C60,
P.ENDUSERNAME AS __C61,
P.ENDUSERPARENTBPID AS __C62,
P.INSTALLEDATADDRESSLINE AS __C63,
P.INSTALLEDATCITY AS __C64,
P.INSTALLEDATCOUNTRY AS __C65,
P.INSTALLEDATSTATE AS __C66,
P.TYPE AS __C67,
P.FPCLCC AS __C68,
P.PARENTSERIALNUMBER AS __C69,
P.PARENTCHASSISTYPE AS __C70,
P.PARENTCHASSISNAME AS __C71,
P.PARENTCHASSISRELEASE AS __C72,
P.JUNOS AS __C73,
P.HASFRU AS __C74,
P.HASINVENTORY AS __C75,
P.HASVERSION AS __C76,
P.HASFPC AS __C77,
P.SOURCE AS __C78,
P.REMARKS AS __C79,
P.DELETED AS __C80,
P.HASCHILDS AS __C81,
P.CONTRACTID AS __C82,
P.CONTRACTSTATUS AS __C83,
P.CONTRACTSTARTTIME AS __C84,
P.CONTRACTENDTIME AS __C85,
P.SUPPORTCOVERAGETYPE AS __C86,
P.COVERAGETYPE AS __C87,
P.PRODDESCRIPTION AS __C88,
P.DUAL AS __C89,
P.INSTALLEDATACCID AS __C90,
P.MANAGEDRESLLERACCID AS __C91,
P.ENDUSERACCID AS __C92,
CNT.CONTRACTID AS __C93,
CNT.STARTDATE AS __C94,
CNT.ENDDATE AS __C95,
CNT.SERVICESKU AS __C96,
CNT.CONTRACTSTATUS AS __C97,
IIB.COUNT AS __C98
FROM CACHE2.CONTRACT CNT
/* CACHE2."contractStatus_idx": CONTRACTSTATUS = 'Active' */
/* WHERE CNT.CONTRACTSTATUS = 'Active'
*/
INNER JOIN TABLE(JOINID VARCHAR(50)=?1) J
/* function */
ON 1=1
INNER JOIN CACHE1.INSTALLBASE P
/* CACHE1."installedAtBpid_idx": INSTALLEDATBPID = J.JOINID */
ON 1=1
/* WHERE (J.JOINID = P.INSTALLEDATBPID)
AND ((P.EQUIPMENTID = CNT.EQUIPMENTID)
AND (LOWER(P.SERIALNUMBER) LIKE '%d2300%'))
*/
INNER JOIN (
SELECT
SERIALNUMBER,
COUNT(*) AS COUNT
FROM CACHE1.INSTALLBASE
GROUP BY SERIALNUMBER
) IIB
/* SELECT
SERIALNUMBER,
COUNT(*) AS COUNT
FROM CACHE1.INSTALLBASE
/++ CACHE1."serialNumber_idx": SERIALNUMBER IS ?3 ++/
WHERE SERIALNUMBER IS ?3
GROUP BY SERIALNUMBER
/++ group sorted ++/: SERIALNUMBER = P.SERIALNUMBER
*/
ON 1=1
WHERE ((CNT.CONTRACTSTATUS = 'Active')
AND (LOWER(P.SERIALNUMBER) LIKE '%d2300%'))
AND ((P.EQUIPMENTID = CNT.EQUIPMENTID)
AND ((P.SERIALNUMBER = IIB.SERIALNUMBER)
AND (J.JOINID = P.INSTALLEDATBPID)))
ORDER BY 15 DESC
LIMIT 10
Count - SELECT
__C0 AS _KEY,
__C1 AS _VAL,
__C2 AS EQUIPMENTID,
__C3 AS INSTALLBASEID,
__C4 AS MATERIALID,
__C5 AS OVERLENGTHMATERIALID,
__C6 AS DEVICENAME,
__C7 AS MODULE,
__C8 AS SUBMODULE,
__C9 AS SUBSUBMODULE,
__C10 AS SUBSUBSUBMODULE,
__C11 AS PARTNUMBER,
__C12 AS REVISION,
__C13 AS MODELNUMBER,
__C14 AS SERIALNUMBER,
__C15 AS DESCRIPTION,
__C16 AS SOFTWARERELEASE,
__C17 AS MATERIALSTATUS,
__C18 AS ROHS2DEPSITION,
__C19 AS LASTORDERTIME,
__C20 AS EOSTIME,
__C21 AS EOSFLAG,
__C22 AS ITEMGROUPDESCRIPTION,
__C23 AS PRODUCTCATEGORY,
__C24 AS PRODUCTFAMILY,
__C25 AS PRODUCTLINE,
__C26 AS PRODUCTNAME,
__C27 AS ISPARENTIBASE,
__C28 AS SERVICEDECLINE,
__C29 AS SERVICEELIGIBLE,
__C30 AS SERVICEOPTIONAL,
__C31 AS SHIPQTR,
__C32 AS SHIPTIME,
__C33 AS DEADWARRANTYTIME,
__C34 AS SOFTWAREWARRANTYTIME,
__C35 AS WARRANTYSTARTTIME,
__C36 AS WARRANTYENDTIME,
__C37 AS WARRANTYTYPE,
__C38 AS DTWARRANTYTYPE,
__C39 AS REGISTRATIONTIME,
__C40 AS MATERIALGROUP,
__C41 AS MATERIALTYPE,
__C42 AS COMPONENTNUMBER,
__C43 AS TOPCOMPONENTIBASE,
__C44 AS INSTALLEDATTHEATER,
__C45 AS INSTALLEDAT,
__C46 AS INSTALLEDATBPID,
__C47 AS MANAGEDRESLLERBPID,
__C48 AS INSTALLEDTIME,
__C49 AS SERVICEDECLINEREASON,
__C50 AS ASSEMBLYNUMBER,
__C51 AS ASSEMBLYREV,
__C52 AS ASSEMPLYHWREV,
__C53 AS RMANUMBER,
__C54 AS CUSTOMERPONUMBER,
__C55 AS SALESORDERNUMBER,
__C56 AS SALESORDERLINENUMBER,
__C57 AS POSDISTRIBUTOR,
__C58 AS RESELLER,
__C59 AS ENDUSERPARENTNAME,
__C60 AS ENDUSERBPID,
__C61 AS ENDUSERNAME,
__C62 AS ENDUSERPARENTBPID,
__C63 AS INSTALLEDATADDRESSLINE,
__C64 AS INSTALLEDATCITY,
__C65 AS INSTALLEDATCOUNTRY,
__C66 AS INSTALLEDATSTATE,
__C67 AS TYPE,
__C68 AS FPCLCC,
__C69 AS PARENTSERIALNUMBER,
__C70 AS PARENTCHASSISTYPE,
__C71 AS PARENTCHASSISNAME,
__C72 AS PARENTCHASSISRELEASE,
__C73 AS JUNOS,
__C74 AS HASFRU,
__C75 AS HASINVENTORY,
__C76 AS HASVERSION,
__C77 AS HASFPC,
__C78 AS SOURCE,
__C79 AS REMARKS,
__C80 AS DELETED,
__C81 AS HASCHILDS,
__C82 AS CONTRACTID,
__C83 AS CONTRACTSTATUS,
__C84 AS CONTRACTSTARTTIME,
__C85 AS CONTRACTENDTIME,
__C86 AS SUPPORTCOVERAGETYPE,
__C87 AS COVERAGETYPE,
__C88 AS PRODDESCRIPTION,
__C89 AS DUAL,
__C90 AS INSTALLEDATACCID,
__C91 AS MANAGEDRESLLERACCID,
__C92 AS ENDUSERACCID,
__C93 AS CONTID,
__C94 AS CONTSTARTDATE,
__C95 AS CONTENDDATE,
__C96 AS CONTSERVICESKU,
__C97 AS CONTSTATUS,
__C98 AS COUNT
FROM PUBLIC.__T0
/* CACHE1."merge_scan" */
ORDER BY 15 DESC
LIMIT 10 OFFSET 0
-Tejas
On Sat, Jan 7, 2017 at 1:07 AM, vkulichenko <va...@gmail.com>
wrote:
> Hi Tejas,
>
> Did you check the execution plan? Are there any scans?
>
> -Val
>
>
>
> --
> View this message in context: http://apache-ignite-users.
> 70518.x6.nabble.com/Affinity-tp9744p9943.html
> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>
Re: Affinity
Posted by vkulichenko <va...@gmail.com>.
Hi Tejas,
Did you check the execution plan? Are there any scans?
-Val
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Affinity-tp9744p9943.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: Affinity
Posted by Tejashwa Kumar Verma <te...@gmail.com>.
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" <nt...@apache.org> 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 <
> tejashwa.verma@gmail.com> 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 <nt...@apache.org>
>> 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 <
>>> tejashwa.verma@gmail.com> 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 <an...@gmail.com> 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 <
>>>>> valentin.kulichenko@gmail.com> 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.
>>>>>>
>>>>>
>>>>>
>>>>
>>>
>>
>
Re: Affinity
Posted by Nikolai Tikhonov <nt...@apache.org>.
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 <
tejashwa.verma@gmail.com> 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 <nt...@apache.org>
> 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 <
>> tejashwa.verma@gmail.com> 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 <an...@gmail.com> 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 <
>>>> valentin.kulichenko@gmail.com> 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.
>>>>>
>>>>
>>>>
>>>
>>
>
Re: Affinity
Posted by Tejashwa Kumar Verma <te...@gmail.com>.
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 <nt...@apache.org>
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.gridgain.org/docs/sql-queries#
> section-distributed-joins
>
> On Thu, Jan 5, 2017 at 2:05 PM, Tejashwa Kumar Verma <
> tejashwa.verma@gmail.com> 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 <an...@gmail.com> 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 <valentin.kulichenko@gmail.com
>>> > 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.
>>>>
>>>
>>>
>>
>
Re: Affinity
Posted by Nikolai Tikhonov <nt...@apache.org>.
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.gridgain.org/docs/sql-queries#section-distributed-joins
On Thu, Jan 5, 2017 at 2:05 PM, Tejashwa Kumar Verma <
tejashwa.verma@gmail.com> 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 <an...@gmail.com> 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 <va...@gmail.com>
>> 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.
>>>
>>
>>
>
Re: Affinity
Posted by Tejashwa Kumar Verma <te...@gmail.com>.
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 <an...@gmail.com> 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 <va...@gmail.com>
> 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.
>>
>
>
Re: Affinity
Posted by vkulichenko <va...@gmail.com>.
The thread continues here:
http://apache-ignite-users.70518.x6.nabble.com/Affinity-td9744i20.html
-Val
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Affinity-tp9744p9882.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: Affinity
Posted by Anil <an...@gmail.com>.
Attached. thanks.
Please let me know if you need any additional details. thanks.
On 2 January 2017 at 17:36, dkarachentsev <dk...@gridgain.com>
wrote:
> Could you please add ignite-client.xml as well?
>
> Thanks!
>
>
>
> --
> View this message in context: http://apache-ignite-users.
> 70518.x6.nabble.com/Affinity-tp9744p9815.html
> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>
Re: Affinity
Posted by dkarachentsev <dk...@gridgain.com>.
Could you please add ignite-client.xml as well?
Thanks!
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Affinity-tp9744p9815.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: Affinity
Posted by Anil <an...@gmail.com>.
Thank you Sergi. i will double check this again from my side.
On 4 January 2017 at 16:50, Sergi Vladykin <se...@gmail.com> wrote:
> Hi,
>
> The query should work because everything is colocated. If not, then it
> would be nice to have a self contained test case.
>
> Sergi
>
> 2017-01-04 14:08 GMT+03:00 Anil <an...@gmail.com>:
>
>> Hi Sergi,
>>
>> Did you get a chance to look into it ? Thanks.
>>
>
>
Re: Affinity
Posted by Sergi Vladykin <se...@gmail.com>.
Hi,
The query should work because everything is colocated. If not, then it
would be nice to have a self contained test case.
Sergi
2017-01-04 14:08 GMT+03:00 Anil <an...@gmail.com>:
> Hi Sergi,
>
> Did you get a chance to look into it ? Thanks.
>
Re: Affinity
Posted by Anil <an...@gmail.com>.
Hi Sergi,
Did you get a chance to look into it ? Thanks.
Re: Affinity
Posted by Anil <an...@gmail.com>.
Thank you Nikolai.
I will wait for Sergi's response. Thanks to both of you.
Re: Affinity
Posted by Nikolai Tikhonov <nt...@apache.org>.
Hi Anil,
It seems that it will not work correctly but I'm not sure 100%. Let's ask
guru SQL.
Sergi,
Could you please look at the following query? (We have two simple table:
Person and PersonDetail collocated by p.equivalentId)
SELECT p.name as name, dupPerson.dupCount as count, pd.startDate as sdt,
pd.endDate as edt from PERSON_CACHE.PERSON p
JOIN (select equivalentId, count(*) dupCount from PERSON_CACHE.PERSON group
by equivalentId ) dupPerson on p.equivalentId = dupPerson.equivalentId //
to get the number of persons with same equivalent Idjoin
DETAILS_CACHE.PersonDetail pd on p.equivalentId = pd.equivalentId
JOIN (select equivalentId, max(enddate) as enddate from
DETAILS_CACHE.PersonDetail group by equivalentId) maxPd on p.equivalentId
= maxPd.equivalentId and maxPd.endDate = pd.endDate
WHERE p.personId = '100'"
On Mon, Jan 2, 2017 at 4:06 PM, Anil <an...@gmail.com> wrote:
> Hi,
>
> I did not use group by query to determine the duplicate count or latest
> details as sub query. i used it as join.
>
> if group by query works in ignite , my join query also should work.
>
> I am not sure if IGNITE-3860 relates to my query. Correct me if I am
> wrong.
>
> Thanks.
>
Re: Affinity
Posted by Anil <an...@gmail.com>.
Hi,
I did not use group by query to determine the duplicate count or latest
details as sub query. i used it as join.
if group by query works in ignite , my join query also should work.
I am not sure if IGNITE-3860 relates to my query. Correct me if I am wrong.
Thanks.
Re: Affinity
Posted by Nikolay Tikhonov <nt...@gridgain.com>.
Anil,
This SQL query will not work correctly due to IGNITE-3860. It contains
subquery which will not distribute on all nodes and will execute locally.
Please, see on limitations
https://apacheignite.readme.io/v1.8/docs/dml#known-limitations
Thanks,
Nikolay
On Mon, Jan 2, 2017 at 3:48 PM, Anil <an...@gmail.com> wrote:
> In local cluster, it looks good to me. But when I move the code to actual
> vm cluster. query returning incorrect data.
>
> Thanks
>
> On 2 January 2017 at 18:15, Anil <an...@gmail.com> wrote:
>
>> Hi Nikolay,
>>
>> Thanks for the response. The use case is very simple.
>>
>> Each Person is associated with number of person details which holds
>> address, start and end dates.
>>
>> *Scenario* - Get person information with latest person detail for given
>> person ids. Person information must include duplicate person with same
>> equivalent Id
>>
>> 1. To get the duplicate persons with same equivalentid -
>>
>> join (select equivalentId, count(*) dupCount from PERSON_CACHE.PERSON
>> group by equivalentId ) dupPerson on p.equivalentId = dupPerson.equivalentId
>>
>> 2. To get the latest person details - based on recent end date
>>
>> join DETAILS_CACHE.PersonDetail pd on p.equivalentId = pd.equivalentId
>> join (select equivalentId, max(enddate) as enddate from
>> DETAILS_CACHE.PersonDetail group by equivalentId) maxPd on p.equivalentId
>> = maxPd.equivalentId and maxPd.endDate = pd.endDate
>>
>> To support group sorted index, i have created index on equivalentId in
>> DETAILS_CACHE.
>>
>> Please let me know if you have any questions.
>> Thanks
>>
>
>
Re: Affinity
Posted by Anil <an...@gmail.com>.
In local cluster, it looks good to me. But when I move the code to actual
vm cluster. query returning incorrect data.
Thanks
On 2 January 2017 at 18:15, Anil <an...@gmail.com> wrote:
> Hi Nikolay,
>
> Thanks for the response. The use case is very simple.
>
> Each Person is associated with number of person details which holds
> address, start and end dates.
>
> *Scenario* - Get person information with latest person detail for given
> person ids. Person information must include duplicate person with same
> equivalent Id
>
> 1. To get the duplicate persons with same equivalentid -
>
> join (select equivalentId, count(*) dupCount from PERSON_CACHE.PERSON
> group by equivalentId ) dupPerson on p.equivalentId = dupPerson.equivalentId
>
> 2. To get the latest person details - based on recent end date
>
> join DETAILS_CACHE.PersonDetail pd on p.equivalentId = pd.equivalentId
> join (select equivalentId, max(enddate) as enddate from
> DETAILS_CACHE.PersonDetail group by equivalentId) maxPd on p.equivalentId
> = maxPd.equivalentId and maxPd.endDate = pd.endDate
>
> To support group sorted index, i have created index on equivalentId in
> DETAILS_CACHE.
>
> Please let me know if you have any questions.
> Thanks
>
Re: Affinity
Posted by Anil <an...@gmail.com>.
Hi Nikolay,
Thanks for the response. The use case is very simple.
Each Person is associated with number of person details which holds
address, start and end dates.
*Scenario* - Get person information with latest person detail for given
person ids. Person information must include duplicate person with same
equivalent Id
1. To get the duplicate persons with same equivalentid -
join (select equivalentId, count(*) dupCount from PERSON_CACHE.PERSON group
by equivalentId ) dupPerson on p.equivalentId = dupPerson.equivalentId
2. To get the latest person details - based on recent end date
join DETAILS_CACHE.PersonDetail pd on p.equivalentId = pd.equivalentId
join (select equivalentId, max(enddate) as enddate from
DETAILS_CACHE.PersonDetail group by equivalentId) maxPd on p.equivalentId
= maxPd.equivalentId and maxPd.endDate = pd.endDate
To support group sorted index, i have created index on equivalentId in
DETAILS_CACHE.
Please let me know if you have any questions.
Thanks
Re: Affinity
Posted by Nikolai Tikhonov <nt...@apache.org>.
I see in your code that you implemented collocation correctly. Person and
PersondDetail with the same equivalentId were mapped on the same partition.
But I don't know what you want to get by sql (don't know your use case).
SQL returns incorrect data?
On Mon, Jan 2, 2017 at 3:21 PM, Anil <an...@gmail.com> wrote:
> Hi Nikolay,
>
> If i am not wrong, unit test case worked. i am able to see the count from
> the group by query. Am I missing anything ?
>
> Thanks.
>
> On 2 January 2017 at 17:43, Nikolai Tikhonov <nt...@apache.org> wrote:
>
>> Hi Anil!
>>
>> In your case we faced with the following issue:
>> https://issues.apache.org/jira/browse/IGNITE-3860. It's mean that
>> ignite will not execute any distributed joins inside of this subquery. For
>> more details about the issue you can read the following thread:
>> http://apache-ignite-developers.2346864.n4.nabble.co
>> m/SELECT-subqueries-in-DML-statements-td13298.html
>>
>> Thanks,
>> Nikolay
>>
>> On Mon, Jan 2, 2017 at 2:21 PM, Anil <an...@gmail.com> wrote:
>>
>>> attached the files. thanks.
>>>
>>>
>>> On 2 January 2017 at 12:07, Anil <an...@gmail.com> wrote:
>>>
>>>> Hi Val,
>>>>
>>>> I created sample unit test and it is working i guess. Not sure why it
>>>> is not working on actual cluster. i will take a look.
>>>>
>>>> Can you please check if that test is correct or not ? thanks.
>>>>
>>>> On 30 December 2016 at 01:24, vkulichenko <
>>>> valentin.kulichenko@gmail.com> wrote:
>>>>
>>>>> Anil,
>>>>>
>>>>> Can you create a unit test that will demonstrate the problem?
>>>>>
>>>>> -Val
>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> View this message in context: http://apache-ignite-users.705
>>>>> 18.x6.nabble.com/Affinity-tp9744p9803.html
>>>>> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>>>>>
>>>>
>>>>
>>>
>>
>
Re: Affinity
Posted by Anil <an...@gmail.com>.
Hi Nikolay,
If i am not wrong, unit test case worked. i am able to see the count from
the group by query. Am I missing anything ?
Thanks.
On 2 January 2017 at 17:43, Nikolai Tikhonov <nt...@apache.org> wrote:
> Hi Anil!
>
> In your case we faced with the following issue: https://issues.apache.org/
> jira/browse/IGNITE-3860. It's mean that ignite will not execute any
> distributed joins inside of this subquery. For more details about the issue
> you can read the following thread: http://apache-ignite-
> developers.2346864.n4.nabble.com/SELECT-subqueries-in-DML-
> statements-td13298.html
>
> Thanks,
> Nikolay
>
> On Mon, Jan 2, 2017 at 2:21 PM, Anil <an...@gmail.com> wrote:
>
>> attached the files. thanks.
>>
>>
>> On 2 January 2017 at 12:07, Anil <an...@gmail.com> wrote:
>>
>>> Hi Val,
>>>
>>> I created sample unit test and it is working i guess. Not sure why it is
>>> not working on actual cluster. i will take a look.
>>>
>>> Can you please check if that test is correct or not ? thanks.
>>>
>>> On 30 December 2016 at 01:24, vkulichenko <valentin.kulichenko@gmail.com
>>> > wrote:
>>>
>>>> Anil,
>>>>
>>>> Can you create a unit test that will demonstrate the problem?
>>>>
>>>> -Val
>>>>
>>>>
>>>>
>>>> --
>>>> View this message in context: http://apache-ignite-users.705
>>>> 18.x6.nabble.com/Affinity-tp9744p9803.html
>>>> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>>>>
>>>
>>>
>>
>
Re: Affinity
Posted by Nikolai Tikhonov <nt...@apache.org>.
Hi Anil!
In your case we faced with the following issue:
https://issues.apache.org/jira/browse/IGNITE-3860. It's mean that ignite
will not execute any distributed joins inside of this subquery. For more
details about the issue you can read the following thread:
http://apache-ignite-developers.2346864.n4.nabble.com/SELECT-subqueries-in-DML-statements-td13298.html
Thanks,
Nikolay
On Mon, Jan 2, 2017 at 2:21 PM, Anil <an...@gmail.com> wrote:
> attached the files. thanks.
>
>
> On 2 January 2017 at 12:07, Anil <an...@gmail.com> wrote:
>
>> Hi Val,
>>
>> I created sample unit test and it is working i guess. Not sure why it is
>> not working on actual cluster. i will take a look.
>>
>> Can you please check if that test is correct or not ? thanks.
>>
>> On 30 December 2016 at 01:24, vkulichenko <va...@gmail.com>
>> wrote:
>>
>>> Anil,
>>>
>>> Can you create a unit test that will demonstrate the problem?
>>>
>>> -Val
>>>
>>>
>>>
>>> --
>>> View this message in context: http://apache-ignite-users.705
>>> 18.x6.nabble.com/Affinity-tp9744p9803.html
>>> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>>>
>>
>>
>
Re: Affinity
Posted by Anil <an...@gmail.com>.
attached the files. thanks.
On 2 January 2017 at 12:07, Anil <an...@gmail.com> wrote:
> Hi Val,
>
> I created sample unit test and it is working i guess. Not sure why it is
> not working on actual cluster. i will take a look.
>
> Can you please check if that test is correct or not ? thanks.
>
> On 30 December 2016 at 01:24, vkulichenko <va...@gmail.com>
> wrote:
>
>> Anil,
>>
>> Can you create a unit test that will demonstrate the problem?
>>
>> -Val
>>
>>
>>
>> --
>> View this message in context: http://apache-ignite-users.705
>> 18.x6.nabble.com/Affinity-tp9744p9803.html
>> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>>
>
>
Re: Affinity
Posted by Anil <an...@gmail.com>.
Hi Val,
I created sample unit test and it is working i guess. Not sure why it is
not working on actual cluster. i will take a look.
Can you please check if that test is correct or not ? thanks.
On 30 December 2016 at 01:24, vkulichenko <va...@gmail.com>
wrote:
> Anil,
>
> Can you create a unit test that will demonstrate the problem?
>
> -Val
>
>
>
> --
> View this message in context: http://apache-ignite-users.
> 70518.x6.nabble.com/Affinity-tp9744p9803.html
> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>
Re: Affinity
Posted by vkulichenko <va...@gmail.com>.
Anil,
Can you create a unit test that will demonstrate the problem?
-Val
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Affinity-tp9744p9803.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: Affinity
Posted by Anil <an...@gmail.com>.
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 <va...@gmail.com>
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.
> 70518.x6.nabble.com/Affinity-tp9744p9757.html
> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>
Re: Affinity
Posted by vkulichenko <va...@gmail.com>.
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.70518.x6.nabble.com/Affinity-tp9744p9757.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: Affinity
Posted by Anil <an...@gmail.com>.
How about AffinityKey<>(personid, equivalentId) for person cache and
AffinityKey<detailId, equivalentId) for details cache ?
Thanks for your help.
On 27 December 2016 at 18:22, Andrey Gura <ag...@apache.org> wrote:
> No, you can't. In this case you should provide person id in order to
> colocate your entries.
>
> On Tue, Dec 27, 2016 at 3:20 PM, Anil <an...@gmail.com> wrote:
> > Hi Andrey,
> >
> > thanks for quick response.
> >
> > Unfortunately, person cache key cannot be used as affinity key for person
> > details in my case.
> >
> > Person {
> >
> > String personId
> >
> > String equivalentId;
> >
> > String name
> >
> > }
> >
> > PersonDetail {
> >
> > String detailId;
> >
> > Strign equivalentId;
> >
> > }
> >
> > Person cache :
> >
> > key = personid
> > value = Person object
> >
> > PersonDetail cache :
> >
> > key = detailId
> > value = PersonDetail
> >
> >
> > the only relation between person and its details is equivalentId
> >
> > can we achieve affinity with above entities ? thanks
> >
> > On 27 December 2016 at 17:09, Andrey Gura <ag...@apache.org> wrote:
> >>
> >> Anil,
> >>
> >> I think in your case Person's key and Persond details key should be
> >> same. Otherwise you will not be able get Person details from cache. Of
> >> course you can keep Person details key in Person instance but it seems
> >> that this relation is transitive and should be removed.
> >>
> >> Other case that I can imagine is some complex key when with each
> >> person could be associated many person details entries. In this case
> >> you should use person key as affinity key for colocation person with
> >> person details on the same affinity nodes set.
> >>
> >> On Tue, Dec 27, 2016 at 2:20 PM, Anil <an...@gmail.com> wrote:
> >> > Hi,
> >> >
> >> > is it mandatory to use key of a cache as part of the affinity key of
> >> > another
> >> > cache ?
> >> >
> >> > i see all examples on github are using the same.
> >> >
> >> > In my scenario, key of a cache (person) cannot be part of affinity key
> >> > of
> >> > another cache (person details). how can we achieve affinity?
> >> >
> >> > Thanks.
> >
> >
>
Re: Affinity
Posted by Andrey Gura <ag...@apache.org>.
No, you can't. In this case you should provide person id in order to
colocate your entries.
On Tue, Dec 27, 2016 at 3:20 PM, Anil <an...@gmail.com> wrote:
> Hi Andrey,
>
> thanks for quick response.
>
> Unfortunately, person cache key cannot be used as affinity key for person
> details in my case.
>
> Person {
>
> String personId
>
> String equivalentId;
>
> String name
>
> }
>
> PersonDetail {
>
> String detailId;
>
> Strign equivalentId;
>
> }
>
> Person cache :
>
> key = personid
> value = Person object
>
> PersonDetail cache :
>
> key = detailId
> value = PersonDetail
>
>
> the only relation between person and its details is equivalentId
>
> can we achieve affinity with above entities ? thanks
>
> On 27 December 2016 at 17:09, Andrey Gura <ag...@apache.org> wrote:
>>
>> Anil,
>>
>> I think in your case Person's key and Persond details key should be
>> same. Otherwise you will not be able get Person details from cache. Of
>> course you can keep Person details key in Person instance but it seems
>> that this relation is transitive and should be removed.
>>
>> Other case that I can imagine is some complex key when with each
>> person could be associated many person details entries. In this case
>> you should use person key as affinity key for colocation person with
>> person details on the same affinity nodes set.
>>
>> On Tue, Dec 27, 2016 at 2:20 PM, Anil <an...@gmail.com> wrote:
>> > Hi,
>> >
>> > is it mandatory to use key of a cache as part of the affinity key of
>> > another
>> > cache ?
>> >
>> > i see all examples on github are using the same.
>> >
>> > In my scenario, key of a cache (person) cannot be part of affinity key
>> > of
>> > another cache (person details). how can we achieve affinity?
>> >
>> > Thanks.
>
>
Re: Affinity
Posted by Anil <an...@gmail.com>.
Hi Andrey,
thanks for quick response.
Unfortunately, person cache key cannot be used as affinity key for person
details in my case.
Person {
String personId
String equivalentId;
String name
}
PersonDetail {
String detailId;
Strign equivalentId;
}
Person cache :
key = personid
value = Person object
PersonDetail cache :
key = detailId
value = PersonDetail
the only relation between person and its details is *equivalentId*
can we achieve affinity with above entities ? thanks
On 27 December 2016 at 17:09, Andrey Gura <ag...@apache.org> wrote:
> Anil,
>
> I think in your case Person's key and Persond details key should be
> same. Otherwise you will not be able get Person details from cache. Of
> course you can keep Person details key in Person instance but it seems
> that this relation is transitive and should be removed.
>
> Other case that I can imagine is some complex key when with each
> person could be associated many person details entries. In this case
> you should use person key as affinity key for colocation person with
> person details on the same affinity nodes set.
>
> On Tue, Dec 27, 2016 at 2:20 PM, Anil <an...@gmail.com> wrote:
> > Hi,
> >
> > is it mandatory to use key of a cache as part of the affinity key of
> another
> > cache ?
> >
> > i see all examples on github are using the same.
> >
> > In my scenario, key of a cache (person) cannot be part of affinity key of
> > another cache (person details). how can we achieve affinity?
> >
> > Thanks.
>
Re: Affinity
Posted by Andrey Gura <ag...@apache.org>.
Anil,
I think in your case Person's key and Persond details key should be
same. Otherwise you will not be able get Person details from cache. Of
course you can keep Person details key in Person instance but it seems
that this relation is transitive and should be removed.
Other case that I can imagine is some complex key when with each
person could be associated many person details entries. In this case
you should use person key as affinity key for colocation person with
person details on the same affinity nodes set.
On Tue, Dec 27, 2016 at 2:20 PM, Anil <an...@gmail.com> wrote:
> Hi,
>
> is it mandatory to use key of a cache as part of the affinity key of another
> cache ?
>
> i see all examples on github are using the same.
>
> In my scenario, key of a cache (person) cannot be part of affinity key of
> another cache (person details). how can we achieve affinity?
>
> Thanks.