My bad still I am using the IN clause with variable parameter. I am exploring other queries. Thanks a ton Rajesh
On 2 Feb 2018 10:41 p.m., "Rajesh Kishore" <[email protected]> wrote: > Hi Andrey, > > This query remains stuck > "EXPLAIN SELECT store.entryID,store.attrName,store.attrValue, > store.attrsType FROM \"dn\".Ignite_DN dn, > \"Ignite_DSAttributeStore\".Ignite_DSAttributeStore > store WHERE dn.entryID in (" > +"(SELECT at1.entryID FROM \"objectclass\".Ignite_ObjectClass at1 > WHERE at1.attrValue = 'subentry' )" > +"UNION " > +"(SELECT at1.entryID FROM \"objectclass\".Ignite_ObjectClass at1 > WHERE at1.attrValue = 'ldapsubentry' )" > +")" > +" AND ( dn.parentDN like 'dc=ignite,%')" > +"AND dn.entryID = store.entryID AND store.attrKind IN ('u','o') order > by store.entryID"; > > > The corresponding explain plan is > > [[SELECT > STORE__Z1.ENTRYID AS __C0_0, > STORE__Z1.ATTRNAME AS __C0_1, > STORE__Z1.ATTRVALUE AS __C0_2, > STORE__Z1.ATTRSTYPE AS __C0_3 > FROM "dn".IGNITE_DN DN__Z0 > /* "dn".RP_DN_IDX: PARENTDN >= 'dc=ignite,' > AND PARENTDN < 'dc=ignite-' > */ > /* WHERE (DN__Z0.PARENTDN LIKE 'dc=ignite,%') > AND (DN__Z0.ENTRYID IN( > (SELECT > AT1__Z2.ENTRYID > FROM "objectclass".IGNITE_OBJECTCLASS AT1__Z2 > /++ "objectclass".OBJECTCLASSNDEXED_ATTRVAL_IDX: ATTRVALUE = > 'subentry' ++/ > WHERE AT1__Z2.ATTRVALUE = 'subentry') > UNION > (SELECT > AT1__Z3.ENTRYID > FROM "objectclass".IGNITE_OBJECTCLASS AT1__Z3 > /++ "objectclass".OBJECTCLASSNDEXED_ATTRVAL_IDX: ATTRVALUE = > 'ldapsubentry' ++/ > WHERE AT1__Z3.ATTRVALUE = 'ldapsubentry'))) > */ > INNER JOIN "Ignite_DSAttributeStore".IGNITE_DSATTRIBUTESTORE STORE__Z1 > /* "Ignite_DSAttributeStore".IGNITE_DSATTRIBUTESTORE_ENTRYID_IDX: > ENTRYID = DN__Z0.ENTRYID */ > ON 1=1 > WHERE (STORE__Z1.ATTRKIND IN('u', 'o')) > AND ((DN__Z0.ENTRYID = STORE__Z1.ENTRYID) > AND ((DN__Z0.PARENTDN LIKE 'dc=ignite,%') > AND (DN__Z0.ENTRYID IN( > (SELECT > AT1__Z2.ENTRYID > FROM "objectclass".IGNITE_OBJECTCLASS AT1__Z2 > /* "objectclass".OBJECTCLASSNDEXED_ATTRVAL_IDX: ATTRVALUE = > 'subentry' */ > WHERE AT1__Z2.ATTRVALUE = 'subentry') > UNION > (SELECT > AT1__Z3.ENTRYID > FROM "objectclass".IGNITE_OBJECTCLASS AT1__Z3 > /* "objectclass".OBJECTCLASSNDEXED_ATTRVAL_IDX: ATTRVALUE = > 'ldapsubentry' */ > WHERE AT1__Z3.ATTRVALUE = 'ldapsubentry'))))) > ORDER BY 1], [SELECT > __C0_0 AS ENTRYID, > __C0_1 AS ATTRNAME, > __C0_2 AS ATTRVALUE, > __C0_3 AS ATTRSTYPE > FROM PUBLIC.__T0 > /* "dn"."merge_sorted" */ > ORDER BY 1 > /* index sorted */]] > > > Note that the subquery has no record > +"(SELECT at1.entryID FROM \"objectclass\".Ignite_ObjectClass at1 > WHERE at1.attrValue = 'subentry' )" > +"UNION " > +"(SELECT at1.entryID FROM \"objectclass\".Ignite_ObjectClass at1 > WHERE at1.attrValue = 'ldapsubentry' )" > > > Any pointers on this ? > > -Rajesh > > > > > > On Fri, Feb 2, 2018 at 10:26 PM, Rajesh Kishore <[email protected]> > wrote: > >> Hey Andrey, >> >> Now , I am getting the result within 3 mins, need to analyze why its >> slower , probably I have to brushup my sql and indexing skills >> this is my explain plan for new query >> >> >> [[SELECT >> ST__Z0.ENTRYID AS __C0_0, >> ST__Z0.ATTRNAME AS __C0_1, >> ST__Z0.ATTRVALUE AS __C0_2, >> ST__Z0.ATTRSTYPE AS __C0_3 >> FROM "dn".IGNITE_DN DN__Z1 >> /* "dn".RP_DN_IDX: PARENTDN >= 'dc=ignite,' >> AND PARENTDN < 'dc=ignite-' >> */ >> /* WHERE DN__Z1.PARENTDN LIKE 'dc=ignite,%' >> */ >> INNER JOIN "Ignite_DSAttributeStore".IGNITE_DSATTRIBUTESTORE ST__Z0 >> /* "Ignite_DSAttributeStore".IGNITE_DSATTRIBUTESTORE_ENTRYID_IDX: >> ENTRYID = DN__Z1.ENTRYID */ >> ON 1=1 >> /* WHERE (ST__Z0.ATTRKIND IN('u', 'o')) >> AND (DN__Z1.ENTRYID = ST__Z0.ENTRYID) >> */ >> INNER JOIN "objectclass".IGNITE_OBJECTCLASS AT1__Z2 >> /* "objectclass".OBJECTCLASSNDEXED_ATTRVAL_IDX: ATTRVALUE = 'person' >> AND ENTRYID = DN__Z1.ENTRYID >> */ >> ON 1=1 >> WHERE ((ST__Z0.ATTRKIND IN('u', 'o')) >> AND ((AT1__Z2.ATTRVALUE = 'person') >> AND (DN__Z1.PARENTDN LIKE 'dc=ignite,%'))) >> AND ((DN__Z1.ENTRYID = AT1__Z2.ENTRYID) >> AND (DN__Z1.ENTRYID = ST__Z0.ENTRYID)) >> ORDER BY 1], [SELECT >> __C0_0 AS ENTRYID, >> __C0_1 AS ATTRNAME, >> __C0_2 AS ATTRVALUE, >> __C0_3 AS ATTRSTYPE >> FROM PUBLIC.__T0 >> /* "dn"."merge_sorted" */ >> ORDER BY 1 >> /* index sorted */]] >> >> -Rajesh >> >> On Fri, Feb 2, 2018 at 9:38 PM, Andrey Mashenkov < >> [email protected]> wrote: >> >>> Rajesh, >>> >>> How much entries returns by subquery inside IN clause? >>> You can try to reduce it with replacing condition like "X.ID in (Select >>> T.ID From T ...)" with " (Select ID From T Where T.ID= X.ID ... Limit >>> 1) == ID". >>> >>> On Fri, Feb 2, 2018 at 6:57 PM, Andrey Mashenkov < >>> [email protected]> wrote: >>> >>>> Hi Rajesh, >>>> >>>> I've also suggested you to replace IN with JOIN in one of prev. >>>> messages. Seems, it was overlooked. >>>> Would you please try this as well? >>>> >>>> On Fri, Feb 2, 2018 at 6:52 PM, Rajesh Kishore <[email protected] >>>> > wrote: >>>> >>>>> Hi Andrey, >>>>> >>>>> Yes , I also came to know about OR but the query is still unresponsive >>>>> when I removed the OR >>>>> >>>>> done [[SELECT >>>>> STORE__Z1.ENTRYID AS __C0_0, >>>>> STORE__Z1.ATTRNAME AS __C0_1, >>>>> STORE__Z1.ATTRVALUE AS __C0_2, >>>>> STORE__Z1.ATTRSTYPE AS __C0_3 >>>>> FROM "dn".IGNITE_DN DN__Z0 >>>>> /* "dn".RP_DN_IDX: PARENTDN >= 'dc=ignite,' >>>>> AND PARENTDN < 'dc=ignite-' >>>>> */ >>>>> /* WHERE (DN__Z0.PARENTDN LIKE 'dc=ignite,%') >>>>> AND (DN__Z0.ENTRYID IN( >>>>> (SELECT >>>>> AT1__Z2.ENTRYID >>>>> FROM "objectclass".IGNITE_OBJECTCLASS AT1__Z2 >>>>> /++ "objectclass".OBJECTCLASSNDEXED_ATTRVAL_IDX: >>>>> ATTRVALUE = 'subentry' ++/ >>>>> WHERE AT1__Z2.ATTRVALUE = 'subentry') >>>>> UNION >>>>> (SELECT >>>>> AT1__Z3.ENTRYID >>>>> FROM "objectclass".IGNITE_OBJECTCLASS AT1__Z3 >>>>> /++ "objectclass".OBJECTCLASSNDEXED_ATTRVAL_IDX: >>>>> ATTRVALUE = 'ldapsubentry' ++/ >>>>> WHERE AT1__Z3.ATTRVALUE = 'ldapsubentry'))) >>>>> */ >>>>> INNER JOIN "Ignite_DSAttributeStore".IGNITE_DSATTRIBUTESTORE STORE__Z1 >>>>> /* "Ignite_DSAttributeStore".IGNITE_DSATTRIBUTESTORE_ENTRYID_IDX: >>>>> ENTRYID = DN__Z0.ENTRYID */ >>>>> ON 1=1 >>>>> WHERE (STORE__Z1.ATTRKIND IN('u', 'o')) >>>>> AND ((DN__Z0.ENTRYID = STORE__Z1.ENTRYID) >>>>> AND ((DN__Z0.PARENTDN LIKE 'dc=ignite,%') >>>>> AND (DN__Z0.ENTRYID IN( >>>>> (SELECT >>>>> AT1__Z2.ENTRYID >>>>> FROM "objectclass".IGNITE_OBJECTCLASS AT1__Z2 >>>>> /* "objectclass".OBJECTCLASSNDEXED_ATTRVAL_IDX: ATTRVALUE = >>>>> 'subentry' */ >>>>> WHERE AT1__Z2.ATTRVALUE = 'subentry') >>>>> UNION >>>>> (SELECT >>>>> AT1__Z3.ENTRYID >>>>> FROM "objectclass".IGNITE_OBJECTCLASS AT1__Z3 >>>>> /* "objectclass".OBJECTCLASSNDEXED_ATTRVAL_IDX: ATTRVALUE = >>>>> 'ldapsubentry' */ >>>>> WHERE AT1__Z3.ATTRVALUE = 'ldapsubentry'))))) >>>>> ORDER BY 1], [SELECT >>>>> __C0_0 AS ENTRYID, >>>>> __C0_1 AS ATTRNAME, >>>>> __C0_2 AS ATTRVALUE, >>>>> __C0_3 AS ATTRSTYPE >>>>> FROM PUBLIC.__T0 >>>>> /* "dn"."merge_sorted" */ >>>>> ORDER BY 1 >>>>> /* index sorted */]] >>>>> >>>>> >>>>> Pls advise >>>>> >>>>> thanks >>>>> Rajesh >>>>> >>>>> On Fri, Feb 2, 2018 at 8:39 PM, Andrey Mashenkov < >>>>> [email protected]> wrote: >>>>> >>>>>> Hi Rajesh, >>>>>> >>>>>> >>FROM "dn".IGNITE_DN DN__Z0 >>>>>> >> /* "dn".IGNITE_DN.__SCAN_ */ >>>>>> >> /* WHERE ((DN__Z0.PARENTDN LIKE 'dc=ignite,%') >>>>>> >> OR ((DN__Z0.RDN = 'dc=ignite') >>>>>> >>>>>> Most probably a table full scan is a reason. >>>>>> Underlying H2 can't use indices when 'OR' condition is used. >>>>>> >>>>>> Try to replace OR with UNION ALL. >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> On Fri, Feb 2, 2018 at 10:33 AM, Rajesh Kishore < >>>>>> [email protected]> wrote: >>>>>> >>>>>>> Thanks Dmitriy, >>>>>>> >>>>>>> The EXPLAIN PLAN >>>>>>> >>>>>>> [[SELECT >>>>>>> STORE__Z1.ENTRYID AS __C0_0, >>>>>>> STORE__Z1.ATTRNAME AS __C0_1, >>>>>>> STORE__Z1.ATTRVALUE AS __C0_2, >>>>>>> STORE__Z1.ATTRSTYPE AS __C0_3 >>>>>>> FROM "dn".IGNITE_DN DN__Z0 >>>>>>> /* "dn".IGNITE_DN.__SCAN_ */ >>>>>>> /* WHERE ((DN__Z0.PARENTDN LIKE 'dc=ignite,%') >>>>>>> OR ((DN__Z0.RDN = 'dc=ignite') >>>>>>> AND (DN__Z0.PARENTDN = ','))) >>>>>>> AND (DN__Z0.ENTRYID IN( >>>>>>> (SELECT >>>>>>> AT1__Z2.ENTRYID >>>>>>> FROM "objectclass".IGNITE_OBJECTCLASS AT1__Z2 >>>>>>> /++ "objectclass".OBJECTCLASSNDEXED_ATTRVAL_IDX: >>>>>>> ATTRVALUE = 'subentry' ++/ >>>>>>> WHERE AT1__Z2.ATTRVALUE = 'subentry') >>>>>>> UNION >>>>>>> (SELECT >>>>>>> AT1__Z3.ENTRYID >>>>>>> FROM "objectclass".IGNITE_OBJECTCLASS AT1__Z3 >>>>>>> /++ "objectclass".OBJECTCLASSNDEXED_ATTRVAL_IDX: >>>>>>> ATTRVALUE = 'ldapsubentry' ++/ >>>>>>> WHERE AT1__Z3.ATTRVALUE = 'ldapsubentry'))) >>>>>>> */ >>>>>>> INNER JOIN "Ignite_DSAttributeStore".IGNITE_DSATTRIBUTESTORE >>>>>>> STORE__Z1 >>>>>>> /* "Ignite_DSAttributeStore".IGNITE_DSATTRIBUTESTORE_ENTRYID_IDX: >>>>>>> ENTRYID = DN__Z0.ENTRYID */ >>>>>>> ON 1=1 >>>>>>> WHERE (STORE__Z1.ATTRKIND IN('u', 'o')) >>>>>>> AND ((DN__Z0.ENTRYID = STORE__Z1.ENTRYID) >>>>>>> AND (((DN__Z0.PARENTDN LIKE 'dc=ignite,%') >>>>>>> OR ((DN__Z0.RDN = 'dc=ignite') >>>>>>> AND (DN__Z0.PARENTDN = ','))) >>>>>>> AND (DN__Z0.ENTRYID IN( >>>>>>> (SELECT >>>>>>> AT1__Z2.ENTRYID >>>>>>> FROM "objectclass".IGNITE_OBJECTCLASS AT1__Z2 >>>>>>> /* "objectclass".OBJECTCLASSNDEXED_ATTRVAL_IDX: ATTRVALUE = >>>>>>> 'subentry' */ >>>>>>> WHERE AT1__Z2.ATTRVALUE = 'subentry') >>>>>>> UNION >>>>>>> (SELECT >>>>>>> AT1__Z3.ENTRYID >>>>>>> FROM "objectclass".IGNITE_OBJECTCLASS AT1__Z3 >>>>>>> /* "objectclass".OBJECTCLASSNDEXED_ATTRVAL_IDX: ATTRVALUE = >>>>>>> 'ldapsubentry' */ >>>>>>> WHERE AT1__Z3.ATTRVALUE = 'ldapsubentry'))))) >>>>>>> ORDER BY 1], [SELECT >>>>>>> __C0_0 AS ENTRYID, >>>>>>> __C0_1 AS ATTRNAME, >>>>>>> __C0_2 AS ATTRVALUE, >>>>>>> __C0_3 AS ATTRSTYPE >>>>>>> FROM PUBLIC.__T0 >>>>>>> /* "Ignite_DSAttributeStore"."merge_sorted" */ >>>>>>> ORDER BY 1 >>>>>>> /* index sorted */]] >>>>>>> >>>>>>> >>>>>>> Thanks >>>>>>> -Rajesh >>>>>>> >>>>>>> On Fri, Feb 2, 2018 at 5:32 AM, Dmitriy Setrakyan < >>>>>>> [email protected]> wrote: >>>>>>> >>>>>>>> Rajesh, can you please show your query here together with execution >>>>>>>> plan? >>>>>>>> >>>>>>>> D. >>>>>>>> >>>>>>>> >>>>>>>> On Thu, Feb 1, 2018 at 8:36 AM, Rajesh Kishore < >>>>>>>> [email protected]> 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" < >>>>>>>>> [email protected]> 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 < >>>>>>>>>> [email protected]> 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 >>>>>>>>>> >>>>>>>>> >>>>>>>> >>>>>>> >>>>>> >>>>>> >>>>>> -- >>>>>> Best regards, >>>>>> Andrey V. Mashenkov >>>>>> >>>>> >>>>> >>>> >>>> >>>> -- >>>> Best regards, >>>> Andrey V. Mashenkov >>>> >>> >>> >>> >>> -- >>> Best regards, >>> Andrey V. Mashenkov >>> >> >> >
