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.scherbakoff@gmail.com]
发送时间: 2016629 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>:

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