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 <[email protected]>
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/
>
