Issues with sub query IN clause

classic Classic list List threaded Threaded
8 messages Options
Rajesh Kishore Rajesh Kishore
Reply | Threaded
Open this post in threaded view
|

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






Andrew Mashenkov Andrew Mashenkov
Reply | Threaded
Open this post in threaded view
|

Re: Issues with sub query IN clause

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.


On Thu, Feb 1, 2018 at 3:44 PM, Rajesh Kishore <[hidden email]> 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
Regards,
Andrew.
Rajesh Kishore Rajesh Kishore
Reply | Threaded
Open this post in threaded view
|

Re: Issues with sub query IN clause

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" <[hidden email]> 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.


On Thu, Feb 1, 2018 at 3:44 PM, Rajesh Kishore <[hidden email]> 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
dsetrakyan dsetrakyan
Reply | Threaded
Open this post in threaded view
|

Re: Issues with sub query IN clause

Rajesh, can you please show your query here together with execution plan?

D.

On Thu, Feb 1, 2018 at 8:36 AM, Rajesh Kishore <[hidden email]> 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" <[hidden email]> 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.


On Thu, Feb 1, 2018 at 3:44 PM, Rajesh Kishore <[hidden email]> 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

Rajesh Kishore Rajesh Kishore
Reply | Threaded
Open this post in threaded view
|

Re: Issues with sub query IN clause

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 <[hidden email]> 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 <[hidden email]> 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" <[hidden email]> 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.


On Thu, Feb 1, 2018 at 3:44 PM, Rajesh Kishore <[hidden email]> 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



Ignite_DN.txt (1K) Download Attachment
Ignite_DSAttributeStore.java (788 bytes) Download Attachment
Ignite_EntryIndexedAttributes.java (1K) Download Attachment
Ignite_ObjectClass.java (1K) Download Attachment
IgniteClient.java (5K) Download Attachment
Rajesh Kishore Rajesh Kishore
Reply | Threaded
Open this post in threaded view
|

Re: Issues with sub query IN clause

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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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" <[hidden email]> 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.


On Thu, Feb 1, 2018 at 3:44 PM, Rajesh Kishore <[hidden email]> 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


Rajesh Kishore Rajesh Kishore
Reply | Threaded
Open this post in threaded view
|

Re: Issues with sub query IN clause

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" <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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" <[hidden email]> 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.


On Thu, Feb 1, 2018 at 3:44 PM, Rajesh Kishore <[hidden email]> 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


Rajesh Kishore Rajesh Kishore
Reply | Threaded
Open this post in threaded view
|

Re: Issues with sub query IN clause

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 <[hidden email]> 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" <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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" <[hidden email]> 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.


On Thu, Feb 1, 2018 at 3:44 PM, Rajesh Kishore <[hidden email]> 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