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