re: ignite group indexing not work problem

[Zhengqingzheng]

Hi Alexei,
I cannot reproduce this issue again.
After restarting my ignite server, my query time reduced to 6ms (remote call).
Once I come up with the same problem again, I will tell you and give you the 
query plan.


Thanks for your help again.



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<mailto: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