答复: ignite group indexing not work problem

[Zhengqingzheng]

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