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:[email protected]]
发送时间: 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
<[email protected]<mailto:[email protected]>>:
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:[email protected]<mailto:[email protected]>]
发送时间: 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
<[email protected]<mailto:[email protected]>>:
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
