You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by Rajesh Kishore <ra...@gmail.com> on 2018/02/01 12:44:05 UTC
Issues with sub query IN clause
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
Re: Issues with sub query IN clause
Posted by Rajesh Kishore <ra...@gmail.com>.
Hi Andrey, everyone,
I am trying to figure out the way that my query should use index instead of
full scan, trying to run following query
SELECT st.entryID,st.attrName,st.attrValue, st.attrsType
FROM
(SELECT entryID as entryID FROM "objectclass".IGNITE_OBJECTCLASS WHERE
attrValue = 'person'
) t
INNER JOIN "Ignite_DSAttributeStore".IGNITE_DSATTRIBUTESTORE st
ON st.entryID = t.entryID
NOTE : "Ignite_DSAttributeStore".IGNITE_DSATTRIBUTESTORE has index defined
on entryID , following is the explain plan obtained, its scanning the
entire
table
SELECT
ST.ENTRYID,
ST.ATTRNAME,
ST.ATTRVALUE,
ST.ATTRSTYPE
FROM "Ignite_DSAttributeStore".IGNITE_DSATTRIBUTESTORE ST
/* "Ignite_DSAttributeStore".IGNITE_DSATTRIBUTESTORE.__SCAN_ */
INNER JOIN (
SELECT
ENTRYID AS ENTRYID
FROM "objectclass".IGNITE_OBJECTCLASS
WHERE ATTRVALUE = 'person'
) T
/* SELECT
ENTRYID AS ENTRYID
FROM "objectclass".IGNITE_OBJECTCLASS
/++ "objectclass".OBJECTCLASSNDEXED_ATTRVAL_IDX: ATTRVALUE =
'person'
AND ENTRYID IS ?1
++/
WHERE (ATTRVALUE = 'person')
AND (ENTRYID IS ?1): ENTRYID = ST.ENTRYID
*/
ON 1=1
WHERE ST.ENTRYID = T.ENTRYID
(1 row, 1 ms)
How should I approach that
"Ignite_DSAttributeStore".IGNITE_DSATTRIBUTESTORE full scan can be avoided ?
Thanks in advance.
-Rajesh
On Fri, Feb 2, 2018 at 11:10 PM, Rajesh Kishore <ra...@gmail.com>
wrote:
> 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" <ra...@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 <ra...@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.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 <
>>>>> rajesh10sinha@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.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
>>>>
>>>
>>>
>>
Re: Issues with sub query IN clause
Posted by Rajesh Kishore <ra...@gmail.com>.
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" <ra...@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 <ra...@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.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 <rajesh10sinha@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.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
>>>
>>
>>
>
Re: Issues with sub query IN clause
Posted by Rajesh Kishore <ra...@gmail.com>.
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 <ra...@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.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 <ra...@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.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
>>
>
>
Re: Issues with sub query IN clause
Posted by Rajesh Kishore <ra...@gmail.com>.
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 <ds...@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 <ra...@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" <an...@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 <ra...@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
>>>
>>
>
Re: Issues with sub query IN clause
Posted by Dmitriy Setrakyan <ds...@apache.org>.
Rajesh, can you please show your query here together with execution plan?
D.
On Thu, Feb 1, 2018 at 8:36 AM, Rajesh Kishore <ra...@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" <an...@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 <ra...@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
>>
>
Re: Issues with sub query IN clause
Posted by Rajesh Kishore <ra...@gmail.com>.
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" <an...@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 <ra...@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
>
Re: Issues with sub query IN clause
Posted by Andrey Mashenkov <an...@gmail.com>.
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 <ra...@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