ignite in-memory sql query performance issue

classic Classic list List threaded Threaded
6 messages Options
kevin.zheng kevin.zheng
Reply | Threaded
Open this post in threaded view
|

ignite in-memory sql query performance issue

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

 

 

 

vdpyatkov vdpyatkov
Reply | Threaded
Open this post in threaded view
|

Re: ignite in-memory sql query performance issue

Hello,

Do you use indexes in a SQL query?
If it does not, that try to create a group index over field1 and fild2.
You can find a description here

On Mon, Jun 6, 2016 at 5:56 AM, Zhengqingzheng <[hidden email]> 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

 

 

 




--
Vladislav Pyatkov
Vladimir Ozerov Vladimir Ozerov
Reply | Threaded
Open this post in threaded view
|

Re: ignite in-memory sql query performance issue

In reply to this post by kevin.zheng
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 <[hidden email]> 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

 

 

 


kevin.zheng kevin.zheng
Reply | Threaded
Open this post in threaded view
|

re: ignite in-memory sql query performance issue

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:[hidden email]]
发送时间: 201666 16:10
收件人: [hidden email]
主题: 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 <[hidden email]> 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

 

 

 

 

kevin.zheng kevin.zheng
Reply | Threaded
Open this post in threaded view
|

re: ignite in-memory sql query performance issue

In reply to this post by Vladimir Ozerov

Hi Vladimir,

I have tried to reset the group index definition.

Using gId and oId as the group index, the time used to retrieve the query reduced to 16ms.

 

In order to speed up the sql queries, do I need to set all the possible group indexes ?

 

Best regards,

Kevin

 

发件人: Vladimir Ozerov [mailto:[hidden email]]
发送时间: 201666 16:10
收件人: [hidden email]
主题: 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 <[hidden email]> 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

 

 

 

 

Vladimir Ozerov Vladimir Ozerov
Reply | Threaded
Open this post in threaded view
|

Re: ignite in-memory sql query performance issue

Hi Kevin,

Currently Ignite uses H2 as underlying database engine. And according to H2 documentation, group indexes are only used when all fields from the index participate in a query. For this reason it might necessary to have several indexes or index groups if multiple different queries are executed against the cache.

Please let me know if you have any further questions.

Vladimir.

On Mon, Jun 6, 2016 at 12:19 PM, Zhengqingzheng <[hidden email]> wrote:

Hi Vladimir,

I have tried to reset the group index definition.

Using gId and oId as the group index, the time used to retrieve the query reduced to 16ms.

 

In order to speed up the sql queries, do I need to set all the possible group indexes ?

 

Best regards,

Kevin

 

发件人: Vladimir Ozerov [mailto:[hidden email]]
发送时间: 201666 16:10
收件人: [hidden email]
主题: 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 <[hidden email]> 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