Re: ignite in-memory sql query performance issue

[Vladimir Ozerov]

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 <zhengqingzh...@huawei.com>
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: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>
> 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
>
>
>
>
>
>
>
>
>

re: ignite in-memory sql query performance issue

[Zhengqingzheng]

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

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

Re: ignite in-memory sql query performance issue

[Vladimir Ozerov]

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 
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 qry = new
> SqlQuery(SelectedCalss.class, qryStr);
>
> qry.setArgs( "97901336", "a88");
>
>
>
> If I call getAll() method like this:
>
> List> 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
>
>
>
>
>
>
>

Re: ignite in-memory sql query performance issue

[Vladislav Pyatkov]

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
https://apacheignite.readme.io/docs/sql-queries

On Mon, Jun 6, 2016 at 5:56 AM, Zhengqingzheng 
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 qry = new
> SqlQuery(SelectedCalss.class, qryStr);
>
> qry.setArgs( "97901336", "a88");
>
>
>
> If I call getAll() method like this:
>
> List> 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

ignite in-memory sql query performance issue

[Zhengqingzheng]

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 qry = new SqlQuery(SelectedCalss.class, 
qryStr);
qry.setArgs( "97901336", "a88");

If I call getAll() method like this:
List> 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