Hi Rajesh, Ignite is a distributed system, testing with one node is really not the way.
You need to consider having multiple nodes and portion and collocate your data before. Thanks, C > On 5 Feb 2018, at 16:36, Rajesh Kishore <[email protected]> wrote: > > 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.png> > > > > Thanks, > Rajesh
