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

Reply via email to