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
>>
>
>
