Thanks Stan for looking into it.
Unfortunately, it still takes 23 sec on 240gb RAM system, the corresponding
EXPLAIN PLAN

[[SELECT
    ST.ENTRYID,
    ST.ATTRNAME,
    ST.ATTRVALUE,
    ST.ATTRSTYPE
FROM "objectclass".IGNITE_OBJECTCLASS T
    /* "objectclass".OBJECTCLASSNDEXED_ATTRVAL_IDX: ATTRVALUE = ?1 */
    /* WHERE T.ATTRVALUE = ?1
    */
INNER JOIN "Ignite_DSAttributeStore".IGNITE_DSATTRIBUTESTORE ST
    /* "Ignite_DSAttributeStore".IGNITE_DSATTRIBUTESTORE_ENTRYID_IDX:
ENTRYID = T.ENTRYID
        AND ENTRYID = T.ENTRYID
     */
    ON 1=1
    /* WHERE (ST.ATTRKIND IN('u', 'o'))
        AND (ST.ENTRYID = T.ENTRYID)
    */
INNER JOIN "dn".IGNITE_DN DNT
    /* "dn".EP_DN_IDX: ENTRYID = ST.ENTRYID
        AND PARENTDN >= 'dc=ignite,'
        AND PARENTDN < 'dc=ignite-'
        AND ENTRYID = ST.ENTRYID
     */
    ON 1=1
WHERE (((ST.ATTRKIND IN('u', 'o'))
    AND (T.ATTRVALUE = ?1))
    AND (DNT.PARENTDN LIKE ?2))
    AND ((ST.ENTRYID = DNT.ENTRYID)
    AND (ST.ENTRYID = T.ENTRYID))]]

Pls advise

Thanks,
Rajesh

On Tue, Feb 13, 2018 at 8:48 PM, Stanislav Lukyanov <stanlukya...@gmail.com>
wrote:

> Hi Rajesh,
>
> While I don't have - and, probably, no one has - any benchmarks comparing
> Ignite vs Berkeley in a single node configuration (as others have said,
> this
> is not really a common use case for Ignite), I can say that performance
> problems you see are likely to be caused by your query structure.
>
> Rule of thumb for Ignite's SQL - avoid nested SELECTs. Also make sure you
> have proper indexes for the fields you use in conditions. Usually you also
> need to make sure that your data is efficiently collocated, but that only
> applies to cases when you have multiple nodes.
>
> I've attempted to optimize the SELECT you've posted - here it is:
>     SELECT st.entryID, st.attrName, st.attrValue, st.attrsType
>         FROM "objectclass".Ignite_ObjectClass as t
>             JOIN "Ignite_DSAttributeStore".IGNITE_DSATTRIBUTESTORE AS st
>                 ON st.entryID = t.entryID
>             JOIN "dn".Ignite_DN AS dnt
>                 ON st.entryID = dnt.entry
>         WHERE t.attrValue= ?
>             AND (st.attrKind = 'u' OR st.attrKind = 'o')
>             AND dnt.parentDN LIKE ?
>
> I can't really verify its correctness, but I guess it can be a decent place
> to start.
>
> Thanks,
> Stan
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>

Reply via email to