Hi, Kevin. 1. I've created an issue https://issues.apache.org/jira/browse/IGNITE-3453 for this and started a discussion on dev list. You can watch the issue to be immediately notified on task progress.
As a workaround try to avoid common fields in index definitions. 2. How do you measure a performance ? 3. You should run query several time before measuring a result to allow JVM to "warm up". It's common test practice. 2016-07-08 5:19 GMT+03:00 Zhengqingzheng <zhengqingzh...@huawei.com>: > Hi Alexei, > > I have tried to use only one group of index, and it works for me. However, > there are 3 issues: > > 1. I still need to define 3 different groups of indexes to speed up > all possible queries. But in this case, only one group index is allowed. > How to define other group indexes without problems? > > 2. The sql query time is about 16ms on local machine. It seems too > slow, as I have tested other sql queries within local machine, and the > query time is about 1 ~ 2 ms > > 3. It seems that no matter using get or sql query methods, the > first query time is always higher than the rest of other queries. > > > > > > And the explain information of my sql with only one group index is listed > as follows (call sql query twice and the first time shows explain info): > > > > Time:78; result1:[[SELECT > > GUID AS __C0 > > FROM "com.huawei.soa.ignite.model.IgniteMetaIndex_Cache".IGNITEMETAINDEX > > /* > "com.huawei.soa.ignite.model.IgniteMetaIndex_Cache"."objId_fieldNum_stringValue": > STRINGVALUE = ?3 > > AND OBJID = ?1 > > AND FIELDNUM = ?2 > > */ > > WHERE (STRINGVALUE = ?3) > > AND ((OBJID = ?1) > > AND (FIELDNUM = ?2))], [SELECT > > __C0 AS GUID > > FROM PUBLIC.__T0 > > /* "com.huawei.soa.ignite.model.IgniteMetaIndex_Cache"."merge_scan" > */]] > > ----------------------------------- > > result size:1 > > ----------------------------------- > > Time used in index cache query: 16ms > > Time:16; result2:[50116926] > > > > > > Best regards, > > Kevin > > > > *发件人:* Alexei Scherbakov [mailto:alexey.scherbak...@gmail.com] > *发送时间:* 2016年7月7日 0:59 > *收件人:* user > *主题:* Re: 答复: ignite group indexing not work problem > > > > Hi, Kevin. > > > > Have you defined other composite indexes including objid, fieldnum fields ? > > If yes, try to disable them and check if query picks up correct index. > > > > > > 2016-07-05 15:45 GMT+03:00 Zhengqingzheng <zhengqingzh...@huawei.com>: > > Hi Alexei, > > Indexing not work again. This time I print the query plan: > > > > [SELECT > > GUID AS __C0 > > FROM "com.huawei.soa.ignite.model.IgniteMetaIndex_Cache".IGNITEMETAINDEX > > /* > "com.huawei.soa.ignite.model.IgniteMetaIndex_Cache"."objId_fieldNum_numValue": > OBJID = ?1 > > AND FIELDNUM = ?2 > > */ > > WHERE (STRINGVALUE = ?3) > > AND ((OBJID = ?1) > > AND (FIELDNUM = ?2))], [SELECT > > __C0 AS GUID > > FROM PUBLIC.__T0 > > /* "com.huawei.soa.ignite.model.IgniteMetaIndex_Cache"."merge_scan" */] > > > > It seems that the group index is broken, as I can see only objId and > fieldnum are used to check the index, however, I defined three columns: > objid, fieldnum and stringvalue > > And the order is incorrect. > > My query is : > > String qryStr = " explain select guid from IgniteMetaIndex where objid=? > and fieldnum=? and stringvalue=? "; > > … > > fieldQry.setArgs( "a1162", 7 ,"18835473249"); > > > > hope you can help me. > > > > > > Best regards, > > Kevin > > *发件人:* Alexei Scherbakov [mailto:alexey.scherbak...@gmail.com] > *发送时间:* 2016年6月29日 0:30 > *收件人:* user > *主题:* Re: ignite group indexing not work problem > > > > Hi, > > > > I've tried the provided sample and found what instead of using > oId_fNum_num index H2 engine prefers oId_fNum_date, > > thus preventing condition on num field to use index. > > > > I think it's incorrect behavior. > > > > Could you disable oId_fNum_date, execute the query again and provide me > with the query plan and execution time ? > > > > You can get query plan from build-in H2 console. Read more about how to > setup console here [1] > > > > [1] https://apacheignite.readme.io/docs/sql-queries#using-h2-debug-console > > > > > > > > > > 2016-06-28 6:08 GMT+03:00 Zhengqingzheng <zhengqingzh...@huawei.com>: > > Hi there, > > My ignite in-memory sql query is very slow. Anyone can help me to figure > out what was wrong? > > > > I am using group indexing to speed up in-memory sql queries. I notice that > my sql query took 2274ms (data set size: 10Million, return result:1). > > > > My query is executed as: > > String qryStr = "select * from UniqueField where oid= ? and fnum= ? and > num= ?"; > > > > String oId="a343"; > > int fNum = 3; > > BigDecimal num = new BigDecimal("510020000982136"); > > > > IgniteCache cache = > igniteMetaUtils.getIgniteCache(IgniteMetaCacheType.UNIQUE_INDEX); // to > get selected cache ,which has been created in some other place > > > > SqlQuery qry = new SqlQuery(UniqueField.class, qryStr); > > qry.setArgs(objId,fieldNum, numVal); > > long start = System.currentTimeMillis(); > > List result= cache.query(qry).getAll(); > > long end = System.currentTimeMillis(); > > System.out.println("Time used in query :"+ (end-start)+"ms"); > > > > And the result shows: Time used in query :2274ms > > > > I have set group indexes, and the model is defined as: > > import java.io.Serializable; > > import java.math.BigDecimal; > > import java.util.Date; > > > > import org.apache.ignite.cache.query.annotations.QuerySqlField; > > > > public class UniqueField implements Serializable > > { > > > > @QuerySqlField > > private String orgId; > > > > @QuerySqlField( > > orderedGroups={ > > @QuerySqlField.Group( > > name="oId_fNum_ msg ", order=1, descending = true), > > @QuerySqlField.Group( > > name="oId_fNum_ num ", order=1, descending = > true), > > @QuerySqlField.Group( > > name="oId_fNum_ date ", order=1, descending = true) > > > > }) > > private String oId; > > > > @QuerySqlField(index=true) > > private String gId; > > > > @QuerySqlField( > > orderedGroups={ > > @QuerySqlField.Group( > > name="oId_fNum_ msg ", order=2, descending = true), > > @QuerySqlField.Group( > > name="oId_fNum_ num ", order=2, descending = > true), > > @QuerySqlField.Group( > > name="oId_fNum_ date ", order=2, descending = true) > > > > }) > > private int fNum; > > > > @QuerySqlField(index=true, @QuerySqlField.Group( > > name="oId_fNum_ msg ", order=3, descending = true)}) > > private String msg; > > > > @QuerySqlField(index=true, @QuerySqlField.Group( > > name="oId_fNum_ num ", order=3, descending = true)}) > > private BigDecimal num; > > > > @QuerySqlField(index=true, @QuerySqlField.Group( > > name="oId_fNum_ date ", order=3, descending = true)}) > > private Date date; > > > > public UniqueField(){}; > > > > public UniqueField( > > String orgId, > > String oId, > > String gId, > > int fNum, > > String msg, > > BigDecimal num, > > Date date > > ){ > > this.orgId=orgId; > > this.oId=oId; > > this.gId = gId; > > this.fNum = fNum; > > this.msg = msg; > > this.num = num; > > this.date = date; > > } > > > > public String getOrgId() > > { > > return orgId; > > } > > > > public void setOrgId(String orgId) > > { > > this.orgId = orgId; > > } > > > > public String getOId() > > { > > return oId; > > } > > > > public void setOId(String oId) > > { > > this.oId = oId; > > } > > > > public String getGid() > > { > > return gId; > > } > > > > public void setGuid(String gId) > > { > > this.gId = gId; > > } > > > > public int getFNum() > > { > > return fNum; > > } > > > > public void setFNum(int fNum) > > { > > this.fNum = fNum; > > } > > > > public String getMsg() > > { > > return msg; > > } > > > > public void setMsg(String msg) > > { > > this.msg = msg; > > } > > > > public BigDecimal getNum() > > { > > return num; > > } > > > > public void setNum(BigDecimal num) > > { > > this.num = num; > > } > > > > public Date getDate() > > { > > return date; > > } > > > > public void setDate(Date date) > > { > > this.date = date; > > } > > > > } > > > > > > > > > > > > > > -- > > > Best regards, > > Alexei Scherbakov > > > > > > -- > > > Best regards, > > Alexei Scherbakov > -- Best regards, Alexei Scherbakov
