re: ignite in-memory sql query performance issue

[Zhengqingzheng]

Hi Vladimir,
I did define group index using orderedgroups annotations.
My real query string is : "select * from UniqueField where  gId= ? and oId=?";
And there is no group index defined for gId and oId.


I have 12Million(actually, 11,770,000 ) records in cache.

My SelectedClass is defined as follows:
package com.huawei.soa.ignite.test;

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(index=true, orderedGroups={@QuerySqlField.Group(
            name="groupIdx", order=0, descending = true)})
    private String oId;

    @QuerySqlField(index=true)
    private String gId;

    @QuerySqlField(index=true, orderedGroups={@QuerySqlField.Group(
            name="groupIdx", order=1, descending = true)})
    private int fNum;

    @QuerySqlField(index=true, orderedGroups={@QuerySqlField.Group(
            name="groupIdx", order=2, descending = true)})
    private String msg;

    @QuerySqlField(index=true, orderedGroups={@QuerySqlField.Group(
            name="groupIdx", order=3, descending = true)})
    private BigDecimal num;

    @QuerySqlField(index=true, orderedGroups={@QuerySqlField.Group(
            name="groupIdx", order=4, 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;
    }

}

发件人: Vladimir Ozerov [mailto:voze...@gridgain.com]
发送时间: 2016年6月6日 16:10
收件人: user@ignite.apache.org
主题: Re: ignite in-memory sql query performance issue

Hi Kevin,

Could you please provide the source code of SelectedClass and estimate number 
of entries in the cache? As Vladislav mentioned, most probably this is a matter 
of setting indexes on relevant fields. If you provide the source code, we will 
be able to give you exact example on how to do that.

Vladimir.

On Mon, Jun 6, 2016 at 5:56 AM, Zhengqingzheng 
<zhengqingzh...@huawei.com<mailto:zhengqingzh...@huawei.com>> wrote:
Hi there,
When using sql query to get a list of objects, I find that the performance is 
really slow. I am wondering, is this normal?
I tried to call a sql query as follows:
String qryStr = "select * from SelectedClass where  field1= ? and field2=?";
SqlQuery<BinaryObject, BinaryObject> qry = new SqlQuery(SelectedCalss.class, 
qryStr);
qry.setArgs( "97901336", "a88");

If I call getAll() method like this:
List<Entry<BinaryObject, BinaryObject>> result = 
cache.withKeepBinary().query(qry).getAll();
It took 160ms to get all the objects (only two objects inside the list)

it takes 1ms to get a querycursor object, like this:
         QueryCursor qc = cache.withKeepBinary().query(qry);
But still need 160ms to put the objects into a list and return;

Best regards,
Kevin