From user-return-17653-archive-asf-public=cust-asf.ponee.io@ignite.apache.org Fri Feb 2 18:11:22 2018 Return-Path: X-Original-To: archive-asf-public@eu.ponee.io Delivered-To: archive-asf-public@eu.ponee.io Received: from cust-asf.ponee.io (cust-asf.ponee.io [163.172.22.183]) by mx-eu-01.ponee.io (Postfix) with ESMTP id D6289180608 for ; Fri, 2 Feb 2018 18:11:22 +0100 (CET) Received: by cust-asf.ponee.io (Postfix) id C4087160C49; Fri, 2 Feb 2018 17:11:22 +0000 (UTC) Delivered-To: archive-asf-public@cust-asf.ponee.io Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by cust-asf.ponee.io (Postfix) with SMTP id 25762160C41 for ; Fri, 2 Feb 2018 18:11:20 +0100 (CET) Received: (qmail 78247 invoked by uid 500); 2 Feb 2018 17:11:20 -0000 Mailing-List: contact user-help@ignite.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: user@ignite.apache.org Delivered-To: mailing list user@ignite.apache.org Received: (qmail 78237 invoked by uid 99); 2 Feb 2018 17:11:19 -0000 Received: from pnap-us-west-generic-nat.apache.org (HELO spamd2-us-west.apache.org) (209.188.14.142) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 02 Feb 2018 17:11:19 +0000 Received: from localhost (localhost [127.0.0.1]) by spamd2-us-west.apache.org (ASF Mail Server at spamd2-us-west.apache.org) with ESMTP id 7675F1A03B1 for ; Fri, 2 Feb 2018 17:11:19 +0000 (UTC) X-Virus-Scanned: Debian amavisd-new at spamd2-us-west.apache.org X-Spam-Flag: NO X-Spam-Score: 1.879 X-Spam-Level: * X-Spam-Status: No, score=1.879 tagged_above=-999 required=6.31 tests=[DKIM_SIGNED=0.1, DKIM_VALID=-0.1, DKIM_VALID_AU=-0.1, HTML_MESSAGE=2, RCVD_IN_DNSWL_NONE=-0.0001, RCVD_IN_MSPIKE_H3=-0.01, RCVD_IN_MSPIKE_WL=-0.01, SPF_PASS=-0.001] autolearn=disabled Authentication-Results: spamd2-us-west.apache.org (amavisd-new); dkim=pass (2048-bit key) header.d=gmail.com Received: from mx1-lw-eu.apache.org ([10.40.0.8]) by localhost (spamd2-us-west.apache.org [10.40.0.9]) (amavisd-new, port 10024) with ESMTP id FQRYMgPKOzLn for ; Fri, 2 Feb 2018 17:11:15 +0000 (UTC) Received: from mail-lf0-f43.google.com (mail-lf0-f43.google.com [209.85.215.43]) by mx1-lw-eu.apache.org (ASF Mail Server at mx1-lw-eu.apache.org) with ESMTPS id 4B79C5F341 for ; Fri, 2 Feb 2018 17:11:14 +0000 (UTC) Received: by mail-lf0-f43.google.com with SMTP id 63so32494064lfv.4 for ; Fri, 02 Feb 2018 09:11:14 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:in-reply-to:references:from:date:message-id:subject:to; bh=/lKo9JTH0GTaHlHs57RwlL8fGCosmCWn4HcTurW8n+s=; b=Noyeu+jeygt+Mjl6HFOhahVRLAe06BmAb1ZNkiVl66GrlhF3vt4YBS7skYb2VcL66+ GIZFoucrbG/CeKHPbrfw/WgGQ8oBfedUdbg7rmi9IfH6hrJrtMom+s9FLOI5cAM23R44 xhqRjmKaM4EUIYKizasCrL+FwKJ+D6Q8VwTk4CWsvdcIJ7dfyJ74k31xyhp3tf59ztgE xukR7KfADYxPW8YXfLbwDMhayzzvBrZrka4y8F74EK8j+pCa7YeYa3HVKBHen4A1Ee8f 5ClZ0HlXwxbKoxkMWXULukibYulOzjxCrIbouCwmPni/M8zeo++usRrvmxv5UcED1QkL nshA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:in-reply-to:references:from:date :message-id:subject:to; bh=/lKo9JTH0GTaHlHs57RwlL8fGCosmCWn4HcTurW8n+s=; b=Ub2aeKK+com4NxFEnFSWqbdE8r4ry+k/xDAMobZK6d+9mzGp0qE4AzD0NZM7PMfGcA LCo1bMAza/PYEThzXOPJ754tYhwjHqERdQagPZg2HLJHHLO2xAQqnVUlpeI4Jsobg+iU rwejU/jhUUopuu8raLvS/gD8Mk450xQPuN+IHTF/7t8yZ5uBIfoA29TtCmphniHkjY8z mhvLGYS1/DVO4dmzJWqSKGzQFp96ffknGGhMGcuqNmr+xLsR2WoEbp3JQxD4kua1fBfE zLNUkdNW6rsFMD10pblPhh4qrZLoPSYFLd8czjpiUDmX3mF2Nf5I8EeEUwzg8ZWYAr7m 3xcg== X-Gm-Message-State: AKwxytchPvQ8jWviurDUnS2kMKXYY91UidUWS8H2u1cXsCscYnZTlHOU h6GiMk32DiRUeOMLMrqdGGa+gOXLVcSZuRnWmQY= X-Google-Smtp-Source: AH8x2255zd6zzaHULgofchTWfqSgBlPDYlqiifTkgzLwZx7CAgLDV0CvdtPmGxXN1CNq4qbetLNTFB7vDzAH+FBFzXA= X-Received: by 10.46.67.16 with SMTP id q16mr2298692lja.16.1517591468231; Fri, 02 Feb 2018 09:11:08 -0800 (PST) MIME-Version: 1.0 Received: by 10.25.89.85 with HTTP; Fri, 2 Feb 2018 09:11:07 -0800 (PST) In-Reply-To: References: From: Rajesh Kishore Date: Fri, 2 Feb 2018 22:41:07 +0530 Message-ID: Subject: Re: Issues with sub query IN clause To: Andrey Mashenkov , user Content-Type: multipart/alternative; boundary="94eb2c0d80286101de05643dcc1e" --94eb2c0d80286101de05643dcc1e Content-Type: text/plain; charset="UTF-8" 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 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 < > andrey.mashenkov@gmail.com> 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 < >> andrey.mashenkov@gmail.com> 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 >>> 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 < >>>> andrey.mashenkov@gmail.com> 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 < >>>>> rajesh10sinha@gmail.com> 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 < >>>>>> dsetrakyan@apache.org> 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 < >>>>>>> rajesh10sinha@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.mashenkov@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 < >>>>>>>>> rajesh10sinha@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 >>>>>>>>> >>>>>>>> >>>>>>> >>>>>> >>>>> >>>>> >>>>> -- >>>>> Best regards, >>>>> Andrey V. Mashenkov >>>>> >>>> >>>> >>> >>> >>> -- >>> Best regards, >>> Andrey V. Mashenkov >>> >> >> >> >> -- >> Best regards, >> Andrey V. Mashenkov >> > > --94eb2c0d80286101de05643dcc1e Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi Andrey,

This= query remains stuck
"EXPLAIN SELECT store.entryID,store.attrName,s= tore.attrValue, store.attrsType FROM \"dn\".Ignite_DN dn, \"= Ignite_DSAttributeStore\".Ignite_DSAttributeStore store WHERE dn.entry= ID in ("
=C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 +"(SELECT at1.= entryID FROM \"objectclass\".Ignite_ObjectClass at1 WHERE at1.att= rValue =3D 'subentry' )"
=C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2= =A0 +"UNION "=C2=A0
=C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 +&q= uot;(SELECT at1.entryID FROM \"objectclass\".Ignite_ObjectClass a= t1 WHERE at1.attrValue =3D 'ldapsubentry' )"
=C2=A0=C2=A0= =C2=A0 =C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 =C2=A0=C2= =A0=C2=A0 =C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 =C2=A0= =C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 =C2= =A0 +")"
=C2=A0 +" AND ( dn.parentDN like 'dc=3Dignit= e,%')"
=C2=A0 +"AND dn.entryID =3D store.entryID AND store= .attrKind IN ('u','o') order by store.entryID";

The corresponding explain plan is

[[SELECT
=C2= =A0=C2=A0=C2=A0 STORE__Z1.ENTRYID AS __C0_0,
=C2=A0=C2=A0=C2=A0 STORE__Z= 1.ATTRNAME AS __C0_1,
=C2=A0=C2=A0=C2=A0 STORE__Z1.ATTRVALUE AS __C0_2,<= br>=C2=A0=C2=A0=C2=A0 STORE__Z1.ATTRSTYPE AS __C0_3
FROM "dn".= IGNITE_DN DN__Z0
=C2=A0=C2=A0=C2=A0 /* "dn".RP_DN_IDX: PARENTD= N >=3D 'dc=3Dignite,'
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0 AND PARENTDN < 'dc=3Dignite-'
=C2=A0=C2=A0=C2=A0=C2=A0 */=
=C2=A0=C2=A0=C2=A0 /* WHERE (DN__Z0.PARENTDN LIKE 'dc=3Dignite,%= 9;)
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 AND (DN__Z0.ENTRYID IN(=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 (SELECT
=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 AT1__Z2.ENTRYID
=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 FROM "objectclass".IGNITE_OB= JECTCLASS AT1__Z2
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0 /++ "objectclass".OBJECTCLASSNDEXED_ATTRVAL_IDX: ATT= RVALUE =3D 'subentry' ++/
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0 WHERE AT1__Z2.ATTRVALUE =3D 'subentry')
=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0 UNION
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0 (SELECT
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0 AT1__Z3.ENTRYID
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 FRO= M "objectclass".IGNITE_OBJECTCLASS AT1__Z3
=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 /++ "objectclass"= ;.OBJECTCLASSNDEXED_ATTRVAL_IDX: ATTRVALUE =3D 'ldapsubentry' ++/=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 WHERE AT1__Z3.ATTRVALUE =3D &#= 39;ldapsubentry')))
=C2=A0=C2=A0=C2=A0 */
INNER JOIN "Ignite= _DSAttributeStore".IGNITE_DSATTRIBUTESTORE STORE__Z1
=C2=A0=C2=A0= =C2=A0 /* "Ignite_DSAttributeStore".IGNITE_DSATTRIBUTESTORE_ENTRY= ID_IDX: ENTRYID =3D DN__Z0.ENTRYID */
=C2=A0=C2=A0=C2=A0 ON 1=3D1
WHE= RE (STORE__Z1.ATTRKIND IN('u', 'o'))
=C2=A0=C2=A0=C2=A0 = AND ((DN__Z0.ENTRYID =3D STORE__Z1.ENTRYID)
=C2=A0=C2=A0=C2=A0 AND ((DN_= _Z0.PARENTDN LIKE 'dc=3Dignite,%')
=C2=A0=C2=A0=C2=A0 AND (DN__Z= 0.ENTRYID IN(
=C2=A0=C2=A0=C2=A0 (SELECT
=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0 AT1__Z2.ENTRYID
=C2=A0=C2=A0=C2=A0 FROM "objectclas= s".IGNITE_OBJECTCLASS AT1__Z2
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0 /* "objectclass".OBJECTCLASSNDEXED_ATTRVAL_IDX: ATTRVALUE = =3D 'subentry' */
=C2=A0=C2=A0=C2=A0 WHERE AT1__Z2.ATTRVALUE =3D= 'subentry')
=C2=A0=C2=A0=C2=A0 UNION
=C2=A0=C2=A0=C2=A0 (SEL= ECT
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 AT1__Z3.ENTRYID
=C2=A0= =C2=A0=C2=A0 FROM "objectclass".IGNITE_OBJECTCLASS AT1__Z3
=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 /* "objectclass".OBJECTCL= ASSNDEXED_ATTRVAL_IDX: ATTRVALUE =3D 'ldapsubentry' */
=C2=A0=C2= =A0=C2=A0 WHERE AT1__Z3.ATTRVALUE =3D 'ldapsubentry')))))
ORDER = BY 1], [SELECT
=C2=A0=C2=A0=C2=A0 __C0_0 AS ENTRYID,
=C2=A0=C2=A0=C2= =A0 __C0_1 AS ATTRNAME,
=C2=A0=C2=A0=C2=A0 __C0_2 AS ATTRVALUE,
=C2= =A0=C2=A0=C2=A0 __C0_3 AS ATTRSTYPE
FROM PUBLIC.__T0
=C2=A0=C2=A0=C2= =A0 /* "dn"."merge_sorted" */
ORDER BY 1
/* index= sorted */]]


Note that the subquery has no record
=C2= =A0 =C2=A0=C2=A0=C2=A0 +"(SELECT at1.entryID FROM \"objectclass\&= quot;.Ignite_ObjectClass at1 WHERE at1.attrValue =3D 'subentry' )&q= uot;
=C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 +"UNION "=C2=A0 =C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 +"(SELECT at1.entryID FROM \&qu= ot;objectclass\".Ignite_ObjectClass at1 WHERE at1.attrValue =3D 'l= dapsubentry' )"


Any pointers on this ?

-Rajesh





O= n Fri, Feb 2, 2018 at 10:26 PM, Rajesh Kishore <rajesh10sinha@gmail.= com> wrote:
Hey Andrey,

Now , I am getting the result wit= hin 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
<= br>
[[SELECT
=C2=A0=C2=A0=C2=A0 ST__Z0.ENTRYID AS __C0_0,
=C2=A0= =C2=A0=C2=A0 ST__Z0.ATTRNAME AS __C0_1,
=C2=A0=C2=A0=C2=A0 ST__Z0.ATTRVA= LUE AS __C0_2,
=C2=A0=C2=A0=C2=A0 ST__Z0.ATTRSTYPE AS __C0_3
FROM &qu= ot;dn".IGNITE_DN DN__Z1
=C2=A0=C2=A0=C2=A0 /* &quo= t;dn".RP_DN_IDX: PARENTDN >=3D 'dc=3Dignite,'
=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 AND PARENTDN < 'dc=3Dignite-'<= br>=C2=A0=C2=A0=C2=A0=C2=A0 */
=C2=A0=C2=A0=C2=A0 /* WHERE DN__Z1= .PARENTDN LIKE 'dc=3Dignite,%'
=C2=A0=C2=A0=C2=A0 */
INNER JO= IN "Ignite_DSAttributeStore".IGNITE_DSATTRIBUTESTORE ST__Z0<= br>=C2=A0=C2=A0=C2=A0 /* "Ignite_DSAttributeStore".IGNITE_DS= ATTRIBUTESTORE_ENTRYID_IDX: ENTRYID =3D DN__Z1.ENTRYID */
=C2=A0=C2= =A0=C2=A0 ON 1=3D1
=C2=A0=C2=A0=C2=A0 /* WHERE (ST__Z0.ATTRKIND IN('= u', 'o'))
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 AND (DN= __Z1.ENTRYID =3D ST__Z0.ENTRYID)
=C2=A0=C2=A0=C2=A0 */
INNER JOIN &qu= ot;objectclass".IGNITE_OBJECTCLASS AT1__Z2
=C2=A0=C2=A0=C2=A0 = /* "objectclass".OBJECTCLASSNDEXED_ATTRVAL_IDX: ATTRVALUE = =3D 'person'
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 AND ENTR= YID =3D DN__Z1.ENTRYID
=C2=A0=C2=A0=C2=A0=C2=A0 */
=C2=A0=C2=A0=C2=A0= ON 1=3D1
WHERE ((ST__Z0.ATTRKIND IN('u', 'o'))
=C2= =A0=C2=A0=C2=A0 AND ((AT1__Z2.ATTRVALUE =3D 'person')
=C2=A0=C2= =A0=C2=A0 AND (DN__Z1.PARENTDN LIKE 'dc=3Dignite,%')))
=C2=A0=C2= =A0=C2=A0 AND ((DN__Z1.ENTRYID =3D AT1__Z2.ENTRYID)
=C2=A0=C2=A0=C2=A0 A= ND (DN__Z1.ENTRYID =3D ST__Z0.ENTRYID))
ORDER BY 1], [S= ELECT
=C2=A0=C2=A0=C2=A0 __C0_0 AS ENTRYID,
=C2=A0=C2=A0=C2=A0 __C0_1= AS ATTRNAME,
=C2=A0=C2=A0=C2=A0 __C0_2 AS ATTRVALUE,
=C2=A0=C2=A0=C2= =A0 __C0_3 AS ATTRSTYPE
FROM PUBLIC.__T0
=C2=A0=C2=A0=C2=A0 /* "= dn"."merge_sorted" */
ORDER BY 1
/* index sorted */]]<= br>
-Rajesh<= br>

On Fri, Feb 2, 2018 at 9:38= PM, Andrey Mashenkov <andrey.mashenkov@gmail.com> = wrote:
Rajesh,

<= /div>
How much entries returns by subquery inside IN clause?=C2=A0
You can try to reduce it with replacing condition like "X.ID=C2=A0in (Select T.ID=C2=A0From T ...)" with " (Select ID From T= Where T.ID=3D X.ID ... Limit 1) =3D=3D ID".

On Fri, F= eb 2, 2018 at 6:57 PM, Andrey Mashenkov <andrey.mashenkov@gmail.c= om> wrote:
Hi Rajesh,

I've also suggested you to replace IN wi= th 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 <rajesh10sinha@gmail.c= om> wrote:
Hi Andrey,

Yes , I also came to know about OR but t= he query is still unresponsive when I removed the OR

=C2=A0done [[S= ELECT
=C2=A0=C2=A0=C2=A0 STORE__Z1.ENTRYID AS __C0_0,
=C2=A0=C2=A0=C2= =A0 STORE__Z1.ATTRNAME AS __C0_1,
=C2=A0=C2=A0=C2=A0 STORE__Z1.ATTRVALUE= AS __C0_2,
=C2=A0=C2=A0=C2=A0 STORE__Z1.ATTRSTYPE AS __C0_3
FROM &qu= ot;dn".IGNITE_DN DN__Z0
=C2=A0=C2=A0=C2=A0 /* "dn".RP_DN_= IDX: PARENTDN >=3D 'dc=3Dignite,'
=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0 AND PARENTDN < 'dc=3Dignite-'
=C2=A0=C2=A0=C2= =A0=C2=A0 */
=C2=A0=C2=A0=C2=A0 /* WHERE (DN__Z0.PARENTDN LIKE 'dc= =3Dignite,%')
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 AND (DN__Z0= .ENTRYID IN(
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 (SELECT
=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 AT1__Z2.ENT= RYID
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 FROM "objectclass&q= uot;.IGNITE_OBJECTCLASS AT1__Z2
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 /++ "objectclass".OBJECTCLASSND= EXED_ATTRVAL_IDX: ATTRVALUE =3D 'subentry' ++/
=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 WHERE AT1__Z2.ATTRVALUE =3D 'subentry= 9;)
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 UNION
=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0 (SELECT
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 AT1__Z3.ENTRYID
=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0 FROM "objectclass".IGNITE_OBJECTCLASS AT1= __Z3
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 = /++ "objectclass".OBJECTCLASSNDEXED_ATTRVAL_IDX: ATTRVALUE = =3D 'ldapsubentry' ++/
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0 WHERE AT1__Z3.ATTRVALUE =3D 'ldapsubentry')))
=C2=A0=C2=A0= =C2=A0 */
INNER JOIN "Ignite_DSAttributeStore".IGNITE_DSA= TTRIBUTESTORE STORE__Z1
=C2=A0=C2=A0=C2=A0 /* "Ignite_DSAttributeSt= ore".IGNITE_DSATTRIBUTESTORE_ENTRYID_IDX: ENTRYID =3D DN__Z0= .ENTRYID */
=C2=A0=C2=A0=C2=A0 ON 1=3D1
WHERE (STORE__Z1.ATTRKIND IN(= 'u', 'o'))
=C2=A0=C2=A0=C2=A0 AND ((DN__Z0.ENTRYID =3D S= TORE__Z1.ENTRYID)
=C2=A0=C2=A0=C2=A0 AND ((DN__Z0.PARENTDN LIKE 'dc= =3Dignite,%')
=C2=A0=C2=A0=C2=A0 AND (DN__Z0.ENTRYID IN(
=C2=A0= =C2=A0=C2=A0 (SELECT
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 AT1__Z2.= ENTRYID
=C2=A0=C2=A0=C2=A0 FROM "objectclass".IGNITE_OBJECTCLA= SS AT1__Z2
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 /* "obje= ctclass".OBJECTCLASSNDEXED_ATTRVAL_IDX: ATTRVALUE =3D 'subent= ry' */
=C2=A0=C2=A0=C2=A0 WHERE AT1__Z2.ATTRVALUE =3D 'subentry&= #39;)
=C2=A0=C2=A0=C2=A0 UNION
=C2=A0=C2=A0=C2=A0 (SELECT
=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 AT1__Z3.ENTRYID
=C2=A0=C2=A0=C2=A0 = FROM "objectclass".IGNITE_OBJECTCLASS AT1__Z3
=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 /* "objectclass".OBJECTCLASSNDE= XED_ATTRVAL_IDX: ATTRVALUE =3D 'ldapsubentry' */
=C2=A0=C2= =A0=C2=A0 WHERE AT1__Z3.ATTRVALUE =3D 'ldapsubentry')))))
ORDER = BY 1], [SELECT
=C2=A0=C2=A0=C2=A0 __C0_0 AS ENTRYID,
=C2=A0=C2=A0=C2= =A0 __C0_1 AS ATTRNAME,
=C2=A0=C2=A0=C2=A0 __C0_2 AS ATTRVALUE,
=C2= =A0=C2=A0=C2=A0 __C0_3 AS ATTRSTYPE
FROM PUBLIC.__T0
=C2=A0=C2=A0=C2= =A0 /* "dn"."merge_sorted" */
ORDER BY 1
/* index= sorted */]]


Pls advise

thanks
=
Rajesh

On Fri, Feb 2, 2018 at 8:39 PM, Andrey Mashenkov <= ;andrey.mas= henkov@gmail.com> wrote:
Hi Rajesh,

>>FROM "dn".IGNITE_DN DN__Z0
>>=C2=A0 =C2=A0/* &q= uot;dn".IGNITE_DN.__SCAN_ */
>>=C2=A0 =C2=A0/* WHERE ((DN__Z0.PARENTD= N LIKE 'dc=3Dignite,%')
>>=C2=A0 =C2=A0 =C2=A0OR ((DN__Z0.RDN =3D = 'dc=3Dignite')

Most probably a table full scan is a reason.=
Underlying H2 can't use indices when 'OR' condition = is used.=C2=A0

Try to replace OR with UNION ALL.




On Fri, Feb 2, 2018 at 10:33 AM, Rajesh Kishore <rajesh10sinha@gmail.com> wrote:
Thanks Dmitriy,

The EXPL= AIN PLAN

[[SELECT
=C2=A0=C2=A0=C2=A0 STORE__Z1.ENTRYID AS __C0_0,=
=C2=A0=C2=A0=C2=A0 STORE__Z1.ATTRNAME AS __C0_1,
=C2=A0=C2=A0=C2=A0 = STORE__Z1.ATTRVALUE AS __C0_2,
=C2=A0=C2=A0=C2=A0 STORE__Z1.ATTRSTYPE AS= __C0_3
FROM "dn".IGNITE_DN DN__Z0
=C2=A0=C2=A0=C2=A0 /* &q= uot;dn".IGNITE_DN.__SCAN_ */
=C2=A0=C2=A0=C2=A0 /* WHERE ((DN__Z0.P= ARENTDN LIKE 'dc=3Dignite,%')
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0 OR ((DN__Z0.RDN =3D 'dc=3Dignite')
=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0 AND (DN__Z0.PARENTDN =3D ',')))
=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 AND (DN__Z0.ENTRYID IN(
=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 (SELECT
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 AT1__Z2.ENTRYID
=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0 FROM "objectclass".IGNITE_OBJECTCLA= SS AT1__Z2
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0 /++ "objectclass".OBJECTCLASSNDEXED_ATTRVAL_IDX: ATTR= VALUE =3D 'subentry' ++/
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0 WHERE AT1__Z2.ATTRVALUE =3D 'subentry')
=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0 UNION
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0 (SELECT
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0 AT1__Z3.ENTRYID
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 FROM &= quot;objectclass".IGNITE_OBJECTCLASS AT1__Z3
=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 /++ "objectclass&q= uot;.OBJECTCLASSNDEXED_ATTRVAL_IDX: ATTRVALUE =3D 'ldapsubentry= 9; ++/
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 WHERE AT1__Z3.ATTRVALU= E =3D 'ldapsubentry')))
=C2=A0=C2=A0=C2=A0 */
INNER JOIN &quo= t;Ignite_DSAttributeStore".IGNITE_DSATTRIBUTESTORE STORE__Z1
= =C2=A0=C2=A0=C2=A0 /* "Ignite_DSAttributeStore".IGNITE_DSATT= RIBUTESTORE_ENTRYID_IDX: ENTRYID =3D DN__Z0.ENTRYID */
=C2=A0=C2=A0= =C2=A0 ON 1=3D1
WHERE (STORE__Z1.ATTRKIND IN('u', 'o'))<= br>=C2=A0=C2=A0=C2=A0 AND ((DN__Z0.ENTRYID =3D STORE__Z1.ENTRYID)
=C2=A0= =C2=A0=C2=A0 AND (((DN__Z0.PARENTDN LIKE 'dc=3Dignite,%')
=C2=A0= =C2=A0=C2=A0 OR ((DN__Z0.RDN =3D 'dc=3Dignite')
=C2=A0=C2=A0=C2= =A0 AND (DN__Z0.PARENTDN =3D ',')))
=C2=A0=C2=A0=C2=A0 AND (DN__= Z0.ENTRYID IN(
=C2=A0=C2=A0=C2=A0 (SELECT
=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0 AT1__Z2.ENTRYID
=C2=A0=C2=A0=C2=A0 FROM "objectclas= s".IGNITE_OBJECTCLASS AT1__Z2
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0 /* "objectclass".OBJECTCLASSNDEXED_ATTRVAL_IDX:= ATTRVALUE =3D 'subentry' */
=C2=A0=C2=A0=C2=A0 WHERE AT1__Z2.AT= TRVALUE =3D 'subentry')
=C2=A0=C2=A0=C2=A0 UNION
=C2=A0=C2=A0= =C2=A0 (SELECT
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 AT1__Z3.ENTRYI= D
=C2=A0=C2=A0=C2=A0 FROM "objectclass".IGNITE_OBJECTCLAS= S AT1__Z3
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 /* "objectclas= s".OBJECTCLASSNDEXED_ATTRVAL_IDX: ATTRVALUE =3D 'ldapsubentry= ' */
=C2=A0=C2=A0=C2=A0 WHERE AT1__Z3.ATTRVALUE =3D 'ldapsubentr= y')))))
ORDER BY 1], [SELECT
=C2=A0=C2=A0=C2=A0 __C0_0 AS ENTRYID= ,
=C2=A0=C2=A0=C2=A0 __C0_1 AS ATTRNAME,
=C2=A0=C2=A0=C2=A0 __C0_2 AS= ATTRVALUE,
=C2=A0=C2=A0=C2=A0 __C0_3 AS ATTRSTYPE
FROM PUBLIC.__T0=C2=A0=C2=A0=C2=A0 /* "Ignite_DSAttributeStore"."merg= e_sorted" */
ORDER BY 1
/* index sorted */]]

<= br>
Thanks=
-Rajesh

On Fri, Feb 2, 2018 at 5:32 AM, Dmitriy Setrakyan <dsetrakyan@apache.org> wrote:
Rajesh, can you please show your query here together w= ith execution plan?

D.


On Thu, Feb 1, 2018 at 8:36 AM, Rajesh Kish= ore <rajesh10sinha@gmail.com> wrote:
Hi Andrey=C2=A0
Thanks = for your response.=C2=A0
I am using native ignite pe= rsistence, saving data locally and as of now I don't have distributed c= ache, having only one node.=C2=A0

By looking at the doc, it does not look like affinity key is appl= icable here.=C2=A0

Pls s= uggest.=C2=A0

Thanks Raj= esh=C2=A0

On 1 Feb 2018 6:27 p.m., "Andrey Mashenkov" <andrey.mashenkov= @gmail.com> wrote:
Hi Rajesh,


Possibl= y, you data is not collocated and subquery return less retults as it execut= es locally.
Try to rewrite IN into JOIN and check if query with q= uery#setDistributedJoins(true) will return expected result.
=
It is recommended=C2=A0
1. replace IN with JOIN du= e to performance issues [1].
2. use data collocation [2] if p= ossible rather than turning on distributed joins.


On Thu, Feb 1, 2018 at 3:44 PM, Rajesh Kishore <r= ajesh10sinha@gmail.com> wrote:
Hi All,

As of now, we have less= than 1 M records , and attribute split into few(3) tables
wi= th index created.
We are using combination of join &=C2= =A0 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 cl= ause and use just the join, the query returns the result.
Not= e that as per EXPLAIN PLAN , the sub query also seems to be using the defin= ed
indexes.

What are the recomme= ndations for using such queries , are there any guidelines, What we are doi= ng wrong here?

Thanks,
Rajesh









--
Best regards,
Andrey V. Mashenkov





--
Best regards,
Andrey V. Mashenkov




--
Best reg= ards,
Andrey V. Mashenkov



--
=
Best regards,
And= rey V. Mashenkov


--94eb2c0d80286101de05643dcc1e--