You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by Zhengqingzheng <zh...@huawei.com> on 2016/07/05 12:45:52 UTC
答复: ignite group indexing not work problem
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]
发送时间: 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 <zh...@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
Re: 答复: 答复: ignite group indexing not work problem
Posted by Alexei Scherbakov <al...@gmail.com>.
Hi, Kevin.
1. I've created an issue https://issues.apache.org/jira/browse/IGNITE-3453
for this and started a discussion on dev list.
You can watch the issue to be immediately notified on task progress.
As a workaround try to avoid common fields in index definitions.
2. How do you measure a performance ?
3. You should run query several time before measuring a result to allow JVM
to "warm up". It's common test practice.
2016-07-08 5:19 GMT+03:00 Zhengqingzheng <zh...@huawei.com>:
> 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 <zh...@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]
> *发送时间:* 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 <zh...@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
>
--
Best regards,
Alexei Scherbakov
答复: 答复: ignite group indexing not work problem
Posted by Zhengqingzheng <zh...@huawei.com>.
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 <zh...@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<ma...@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 <zh...@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
Re: 答复: ignite group indexing not work problem
Posted by Alexei Scherbakov <al...@gmail.com>.
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 <zh...@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]
> *发送时间:* 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 <zh...@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