ignite-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Alexei Scherbakov <alexey.scherbak...@gmail.com>
Subject Re: ignite group indexing not work problem
Date Tue, 28 Jun 2016 16:29:49 GMT
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 <zhengqingzheng@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

Mime
View raw message