ignite-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Zhengqingzheng <zhengqingzh...@huawei.com>
Subject 答复: 答复: ignite group indexing not work problem
Date Fri, 08 Jul 2016 02:19:53 GMT
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.scherbakoff@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 <zhengqingzheng@huawei.com<mailto:zhengqingzheng@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.scherbakoff@gmail.com<mailto:alexey.scherbakoff@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 <zhengqingzheng@huawei.com<mailto: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



--

Best regards,
Alexei Scherbakov
Mime
View raw message