slow query performance against berkley db

[Rajesh Kishore]

Hi,

We are in the process of evaluating Ignite native persistence against
berkely db. For some reason Ignite query does not seem to be performant the
way application code behaves against berkley db

Background:
Berkley db - As of now, we have berkley db for our application and the data
is stored as name value pair as byte stream in the berkley db's native file
system.

Ignite DB - We are using Ignite DB's native persistence file system.
Created appropriate index and retrieving data using SQL involving multiple
joins.

Ignite configuration : with native persistence enabled , only one node

Data: As of now in the main table we have only *.1 M records *and in
supporting tables we have around 2 million records

Ignite sql query used

SELECT f.entryID,f.attrName,f.attrValue, f.attrsType FROM
    ( select st.entryID,st.attrName,st.attrValue, st.attrsType from
            (SELECT at1.entryID FROM "objectclass".Ignite_ObjectClass at1
WHERE  at1.attrValue= ? ) t
            INNER JOIN
            "Ignite_DSAttributeStore".IGNITE_DSATTRIBUTESTORE st ON
st.entryID = t.entryID  WHERE st.attrKind IN ('u','o')
    ) f
   INNER JOIN  (SELECT entryID from "dn".Ignite_DN where parentDN like ? )
dnt ON f.entryID = dnt.entry

The corresponding EXPLAIN PLAN



[[SELECT
    F__Z3.ENTRYID AS __C0_0,
    F__Z3.ATTRNAME AS __C0_1,
    F__Z3.ATTRVALUE AS __C0_2,
    F__Z3.ATTRSTYPE AS __C0_3
FROM (
    SELECT
        ST__Z2.ENTRYID,
        ST__Z2.ATTRNAME,
        ST__Z2.ATTRVALUE,
        ST__Z2.ATTRSTYPE
    FROM (
        SELECT
            AT1__Z0.ENTRYID
        FROM "objectclass".IGNITE_OBJECTCLASS AT1__Z0
        WHERE AT1__Z0.ATTRVALUE = ?1
    ) T__Z1
    INNER JOIN "Ignite_DSAttributeStore".IGNITE_DSATTRIBUTESTORE ST__Z2
        ON 1=1
    WHERE (ST__Z2.ATTRKIND IN('u', 'o'))
        AND (ST__Z2.ENTRYID = T__Z1.ENTRYID)
) F__Z3
    /* SELECT
        ST__Z2.ENTRYID,
        ST__Z2.ATTRNAME,
        ST__Z2.ATTRVALUE,
        ST__Z2.ATTRSTYPE
    FROM (
        SELECT
            AT1__Z0.ENTRYID
        FROM "objectclass".IGNITE_OBJECTCLASS AT1__Z0
        WHERE AT1__Z0.ATTRVALUE = ?1
    ) T__Z1
        /++ SELECT
            AT1__Z0.ENTRYID
        FROM "objectclass".IGNITE_OBJECTCLASS AT1__Z0
            /++ "objectclass".OBJECTCLASSNDEXED_ATTRVAL_IDX: ATTRVALUE = ?1
++/
        WHERE AT1__Z0.ATTRVALUE = ?1
         ++/
    INNER JOIN "Ignite_DSAttributeStore".IGNITE_DSATTRIBUTESTORE ST__Z2
        /++ "Ignite_DSAttributeStore".IGNITE_DSATTRIBUTESTORE_ENTRYID_IDX:
ENTRYID = T__Z1.ENTRYID ++/
        ON 1=1
    WHERE (ST__Z2.ATTRKIND IN('u', 'o'))
        AND (ST__Z2.ENTRYID = T__Z1.ENTRYID)
     */
INNER JOIN (
    SELECT
        __Z4.ENTRYID
    FROM "dn".IGNITE_DN __Z4
    WHERE __Z4.PARENTDN LIKE ?2
) DNT__Z5
    /* SELECT
        __Z4.ENTRYID
    FROM "dn".IGNITE_DN __Z4
        /++ "dn".EP_DN_IDX: ENTRYID IS ?3 ++/
    WHERE (__Z4.ENTRYID IS ?3)
        AND (__Z4.PARENTDN LIKE ?2): ENTRYID = F__Z3.ENTRYID
     */
    ON 1=1
WHERE F__Z3.ENTRYID = DNT__Z5.ENTRYID
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 */]]


Any pointers , how should I proceed , Following is the JFR report for the
code used
cursor = cache.query(new SqlFieldsQuery(query).setEnforceJoinOrder(true);
cursor.getAll();


[image: Inline image 1]



Thanks,
Rajesh