You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by Skollur <Sk...@idbny.com> on 2018/09/27 17:40:11 UTC
Ignite Query Slow
I have 3 cache store and each has its own table. When I tried to do simple
join, query is taking longer in DBeaver. Server topology is ver=4,
servers=1, clients=1, CPUs=4, offheap=3.2GB, heap=6.0GB
Cache 1. GroupCache"."[GROUP]" has 100k records
Cache 2. "GroupMemberCache".GROUP_MEMBER has 200k records
Cache 3. "CustomerCache".CUSTOMER has 200k records
Query is as below
SELECT CUST.Name
FROM "GroupCache"."[GROUP]" GRP
INNER JOIN "GroupMemberCache".GROUP_MEMBER GM
ON GRP.Group_Customer_ID = 44
INNER JOIN "CustomerCache".CUSTOMER CUST
ON GM.Customer_ID = CUST.Customer_ID
AND GM.Record_Is_Valid = 'Y'
AND GRP.Record_Is_Valid = 'Y'
AND GRP.Group_Customer_ID = GM.Group_Customer_ID
AND GM.Record_Is_Valid = 'Y'
Query is executing longer than 2000 seconds. Any suggestion on this?
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: Ignite Query Slow
Posted by Skollur <Sk...@idbny.com>.
1. I have defined primary key - dwId
2. Not defined affinity_key since there is no Forgein Key in table.
3.
CacheConfiguration ccfg = new CacheConfiguration();
ccfg.setName(cacheName);
ccfg.setCacheMode(CacheMode.PARTITIONED);
ccfg.setAtomicityMode(CacheAtomicityMode.ATOMIC);
ccfg.setPeerClassLoadingEnabled(true);
The code i am using is generated from ignite web console.
Code is below.
CacheConfiguration
ccfg=CacheConfigurationInitialize.initCacheConfiguration(
IdbCacheConstants.GROUP_MEMBER_CACHE,
jdbcTypeGroupMember(IdbCacheConstants.GROUP_MEMBER_CACHE));
ArrayList<QueryEntity> qryEntities = new ArrayList<>();
QueryEntity qryEntity = new QueryEntity();
qryEntity.setKeyType("java.lang.Long");
qryEntity.setValueType("com.idb.cache.model.GroupMember");
qryEntity.setTableName("Group_Member");
qryEntity.setKeyFieldName("dwId");
HashSet<String> keyFields = new HashSet<>();
keyFields.add("dwId");
qryEntity.setKeyFields(keyFields);
LinkedHashMap<String, String> fields = new LinkedHashMap<>();
fields.put("dwId", "java.lang.Long");
fields.put("groupCustomerId", "java.lang.Long");
fields.put("customerId", "java.lang.Long");
fields.put("role", "java.lang.String");
fields.put("isPrimary", "java.lang.String");
fields.put("recordValidFromDate", "java.sql.Date");
fields.put("recordValidToDate", "java.sql.Date");
fields.put("recordIsValid", "java.lang.String");
fields.put("recordCreatedTime", "java.sql.Timestamp");
fields.put("recordCreatedBy", "java.lang.String");
fields.put("recordUpdatedTime", "java.sql.Timestamp");
fields.put("recordUpdatedBy", "java.lang.String");
fields.put("dwBatch", "java.lang.Long");
fields.put("dwSourcecode", "java.lang.String");
fields.put("dwTimestamp", "java.sql.Timestamp");
qryEntity.setFields(fields);
HashMap<String, String> aliases = new HashMap<>();
aliases.put("dwId", "DW_Id");
aliases.put("groupCustomerId", "Group_Customer_ID");
aliases.put("customerId", "Customer_ID");
aliases.put("recordValidFromDate", "Record_Valid_From_Date");
aliases.put("recordValidToDate", "Record_Valid_To_Date");
aliases.put("recordIsValid", "Record_Is_Valid");
aliases.put("recordCreatedTime", "Record_Created_Time");
aliases.put("recordCreatedBy", "Record_Created_By");
aliases.put("recordUpdatedTime", "Record_Updated_Time");
aliases.put("recordUpdatedBy", "Record_Updated_By");
aliases.put("dwBatch", "DW_Batch");
aliases.put("dwSourcecode", "DW_SourceCode");
aliases.put("dwTimestamp", "DW_TimeStamp");
qryEntity.setAliases(aliases);
qryEntities.add(qryEntity);
ccfg.setQueryEntities(qryEntities);
return ccfg;
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
RE: Ignite Query Slow
Posted by Skollur <Sk...@idbny.com>.
Any help on this?
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
RE: Ignite Query Slow
Posted by Skollur <Sk...@idbny.com>.
Thank you..Tried @QuerySQLFields...but column name not appeared same as
database table(i.e underscore("_") is missing in ignite). Hence I have added
index to qryEntity and noticed that Query takes about 7 seconds (i.e select
* from customercache.customer) for against ignite cache data of 200,000
records to fetch 200 records in dbeaver. Same query against database comes
in fractions of seconds. I have INDEX created for two fields. I am running
cache with 8 GB Memory, one server node. The codes are mostly generated from
ignite web console. Is there anything am I missing from below code or
anything can be improved on below code? Any suggestion to improve this
helps.
Cache Code
public class CustomerCache {
public static CacheConfiguration cacheCustomerCache() throws Exception {
CacheConfiguration ccfg = new CacheConfiguration();
ccfg.setName("CustomerCache");
ccfg.setCacheMode(CacheMode.PARTITIONED);
ccfg.setAtomicityMode(CacheAtomicityMode.ATOMIC);
CacheJdbcPojoStoreFactory cacheStoreFactory = new
CacheJdbcPojoStoreFactory();
cacheStoreFactory.setDataSourceFactory(new Factory<DataSource>() {
@Override public DataSource create() {
return DataSources.INSTANCE_DB;
};
});
cacheStoreFactory.setDialect(new SQLServerDialect());
cacheStoreFactory.setTypes(jdbcTypeCustomer(ccfg.getName()));
ccfg.setCacheStoreFactory(cacheStoreFactory);
ccfg.setReadThrough(true);
ccfg.setWriteThrough(true);
ArrayList<QueryEntity> qryEntities = new ArrayList<>();
QueryEntity qryEntity = new QueryEntity();
qryEntity.setKeyType("java.lang.Long");
qryEntity.setValueType("com.model.Customer");
LinkedHashMap<String, String> fields = new LinkedHashMap<>();
fields.put("dwId", "java.lang.Long");
fields.put("customerId", "java.lang.Long");
fields.put("customerName", "java.lang.String");
qryEntity.setFields(fields);
HashMap<String, String> aliases = new HashMap<>();
aliases.put("dwId", "DW_Id");
aliases.put("customerId", "Customer_ID");
aliases.put("customerName", "Customer_Name");
qryEntity.setAliases(aliases);
/Adding Index
ArrayList<QueryIndex> indexes = new ArrayList<>();
QueryIndex index = new QueryIndex();
index.setName("NonClustered_Index_ID");
index.setIndexType(QueryIndexType.SORTED);
LinkedHashMap<String, Boolean> indFlds = new LinkedHashMap<>();
indFlds.put("dwId", true);
indFlds.put("customerId", true);
index.setFields(indFlds);
indexes.add(index);
qryEntity.setIndexes(indexes);
qryEntities.add(qryEntity);
ccfg.setQueryEntities(qryEntities);
return ccfg;
}
private static JdbcType jdbcTypeCustomer(String cacheName) {
JdbcType type = new JdbcType();
type.setCacheName(cacheName);
type.setKeyType("java.lang.Long");
type.setValueType("com.model.Customer");
type.setDatabaseSchema("dbo");
type.setDatabaseTable("Customer");
type.setValueFields(
new JdbcTypeField(Types.BIGINT, "DW_Id", long.class, "dwId"),
new JdbcTypeField(Types.BIGINT, "Customer_ID", Long.class,
"customerId"),
new JdbcTypeField(Types.VARCHAR, "First_Name", String.class,
"customerName")
);
return type;
}
}
POJO Class
----------
public class Customer implements Serializable {
private static final long serialVersionUID = 0L;
private long dwId;
private Long customerId;
}
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
RE: Ignite Query Slow
Posted by Stanislav Lukyanov <st...@gmail.com>.
The call
setIndexedTypes(Long.class, Person.class)
will search for all `@QuerySqlField` fields in Person and create indexes for all of them with `index=true`.
For example, if your class looks like this
class Person {
@QuerySqlField(index = true)
private String name;
@QuerySqlField(index = true)
private int age;
}
then the call
setIndexedTypes(Long.class, Person.class)
will create indexes for both `name` and `age`.
Thanks,
Stan
From: Skollur
Sent: 2 октября 2018 г. 16:51
To: user@ignite.apache.org
Subject: Re: Ignite Query Slow
Documentation has example as ccfg.setIndexedTypes(Long.class, Person.class);.
I believe this is single field. How to register multiple indexes for
CacheConfiguration?
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: Ignite Query Slow
Posted by Skollur <Sk...@idbny.com>.
Documentation has example as ccfg.setIndexedTypes(Long.class, Person.class);.
I believe this is single field. How to register multiple indexes for
CacheConfiguration?
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: Ignite Query Slow
Posted by Andrey Mashenkov <an...@gmail.com>.
Hi,
Ignite provides 2 way for configuring indices: queryEntity and annotation
based.
Seems, you either forget to setIndesTypes or tring mix both of these
approaches.
Please, find examples in documentation available via link I've provided
earlier.
пн, 1 окт. 2018 г., 18:05 Skollur <Sk...@idbny.com>:
> I tried with setting up field with @QuerySqlField (index = true) and still
> slow.
>
> Example :
> /** Value for customerId. */
> @QuerySqlField (index = true)
> private Long customerId
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>
Re: Ignite Query Slow
Posted by Skollur <Sk...@idbny.com>.
I tried with setting up field with @QuerySqlField (index = true) and still
slow.
Example :
/** Value for customerId. */
@QuerySqlField (index = true)
private Long customerId
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: Ignite Query Slow
Posted by Andrey Mashenkov <an...@gmail.com>.
Please take a look at this.
https://apacheignite.readme.io/v2.6/docs/indexes#section-queryentity-based-configuration
29 сент. 2018 г. 3:41 пользователь "Skollur" <Sk...@idbny.com> написал:
Thank you for suggestion. Can you give example how to create secondary
indices?
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: Ignite Query Slow
Posted by Skollur <Sk...@idbny.com>.
Thank you for suggestion. Can you give example how to create secondary
indices?
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: Ignite Query Slow
Posted by Andrey Mashenkov <an...@gmail.com>.
Hi,
Please, try to create secondary indices on join columns, otherwise query
will fall into full table scan.
Then if you will see SCANs, as next step, you can try to rewrite your query
with different tables join order. Sometimes, underline H2 can change join
order to non optimal. In that case qry.setEnforceJoinOrder(true) may be
helpful.
Looks like there should be a single lookup on ID column, and 2 index scans
for joining.
пт, 28 сент. 2018 г., 19:02 Skollur <Sk...@idbny.com>:
> Here is the explain query
>
> # PLAN
> 1 "SELECT
> ADDR__Z2.ADDRESS_LINE_1 AS __C0_0,
> ADDR__Z2.ADDRESS_LINE_2 AS __C0_1,
> ADDR__Z2.ADDRESS_LINE_3 AS __C0_2,
> ADDR__Z2.STREET AS __C0_3,
> ADDR__Z2.CITY AS __C0_4,
> ADDR__Z2.STATE AS __C0_5,
> ADDR__Z2.COUNTRY AS __C0_6,
> ADDR__Z2.ZIP_POSTAL AS __C0_7
> FROM "GroupAddressCache".GROUP_ADDRESS GA__Z1
> /* "GroupAddressCache".GROUP_ADDRESS.__SCAN_ */
> /* WHERE (GA__Z1.ADDRESS_TYPE = 'Mailing')
> AND (GA__Z1.RECORD_IS_VALID = 'Y')
> */
> INNER JOIN "GroupCache"."[GROUP]" GRP__Z0
> /* "GroupCache"."[GROUP]".__SCAN_ */
> ON 1=1
> /* WHERE (GRP__Z0.RECORD_IS_VALID = 'Y')
> AND ((GRP__Z0.GROUP_CUSTOMER_ID = 44)
> AND (GRP__Z0.GROUP_CUSTOMER_ID = GA__Z1.GROUP_CUSTOMER_ID))
> */
> INNER JOIN "AddressCache".ADDRESS ADDR__Z2
> /* "AddressCache"."_key_PK_proxy": DW_ID = GA__Z1.ADDRESS_ID */
> ON 1=1
> WHERE (GA__Z1.ADDRESS_ID = ADDR__Z2.DW_ID)
> AND ((GA__Z1.ADDRESS_TYPE = 'Mailing')
> AND ((GA__Z1.RECORD_IS_VALID = 'Y')
> AND ((GRP__Z0.GROUP_CUSTOMER_ID = GA__Z1.GROUP_CUSTOMER_ID)
> AND ((GRP__Z0.GROUP_CUSTOMER_ID = 44)
> AND (GRP__Z0.RECORD_IS_VALID = 'Y')))))"
> 2 "SELECT
> __C0_0 AS ADDRESS_LINE_1,
> __C0_1 AS ADDRESS_LINE_2,
> __C0_2 AS ADDRESS_LINE_3,
> __C0_3 AS STREET,
> __C0_4 AS CITY,
> __C0_5 AS STATE,
> __C0_6 AS COUNTRY,
> __C0_7 AS ZIP_POSTAL
> FROM PUBLIC.__T0
> /* PUBLIC."merge_scan" */"
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>
Re: Ignite Query Slow
Posted by Skollur <Sk...@idbny.com>.
Here is the explain query
# PLAN
1 "SELECT
ADDR__Z2.ADDRESS_LINE_1 AS __C0_0,
ADDR__Z2.ADDRESS_LINE_2 AS __C0_1,
ADDR__Z2.ADDRESS_LINE_3 AS __C0_2,
ADDR__Z2.STREET AS __C0_3,
ADDR__Z2.CITY AS __C0_4,
ADDR__Z2.STATE AS __C0_5,
ADDR__Z2.COUNTRY AS __C0_6,
ADDR__Z2.ZIP_POSTAL AS __C0_7
FROM "GroupAddressCache".GROUP_ADDRESS GA__Z1
/* "GroupAddressCache".GROUP_ADDRESS.__SCAN_ */
/* WHERE (GA__Z1.ADDRESS_TYPE = 'Mailing')
AND (GA__Z1.RECORD_IS_VALID = 'Y')
*/
INNER JOIN "GroupCache"."[GROUP]" GRP__Z0
/* "GroupCache"."[GROUP]".__SCAN_ */
ON 1=1
/* WHERE (GRP__Z0.RECORD_IS_VALID = 'Y')
AND ((GRP__Z0.GROUP_CUSTOMER_ID = 44)
AND (GRP__Z0.GROUP_CUSTOMER_ID = GA__Z1.GROUP_CUSTOMER_ID))
*/
INNER JOIN "AddressCache".ADDRESS ADDR__Z2
/* "AddressCache"."_key_PK_proxy": DW_ID = GA__Z1.ADDRESS_ID */
ON 1=1
WHERE (GA__Z1.ADDRESS_ID = ADDR__Z2.DW_ID)
AND ((GA__Z1.ADDRESS_TYPE = 'Mailing')
AND ((GA__Z1.RECORD_IS_VALID = 'Y')
AND ((GRP__Z0.GROUP_CUSTOMER_ID = GA__Z1.GROUP_CUSTOMER_ID)
AND ((GRP__Z0.GROUP_CUSTOMER_ID = 44)
AND (GRP__Z0.RECORD_IS_VALID = 'Y')))))"
2 "SELECT
__C0_0 AS ADDRESS_LINE_1,
__C0_1 AS ADDRESS_LINE_2,
__C0_2 AS ADDRESS_LINE_3,
__C0_3 AS STREET,
__C0_4 AS CITY,
__C0_5 AS STATE,
__C0_6 AS COUNTRY,
__C0_7 AS ZIP_POSTAL
FROM PUBLIC.__T0
/* PUBLIC."merge_scan" */"
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: Ignite Query Slow
Posted by eugene miretsky <eu...@gmail.com>.
1) Can you post the query plan?
2) what is your CacheConfiguration.queryParallelism setting?
3) What is your affinity_key?
4) What are your primary keys?
On Thu, Sep 27, 2018 at 1:40 PM Skollur <Sk...@idbny.com> wrote:
> I have 3 cache store and each has its own table. When I tried to do simple
> join, query is taking longer in DBeaver. Server topology is ver=4,
> servers=1, clients=1, CPUs=4, offheap=3.2GB, heap=6.0GB
>
> Cache 1. GroupCache"."[GROUP]" has 100k records
> Cache 2. "GroupMemberCache".GROUP_MEMBER has 200k records
> Cache 3. "CustomerCache".CUSTOMER has 200k records
>
> Query is as below
> SELECT CUST.Name
> FROM "GroupCache"."[GROUP]" GRP
> INNER JOIN "GroupMemberCache".GROUP_MEMBER GM
> ON GRP.Group_Customer_ID = 44
> INNER JOIN "CustomerCache".CUSTOMER CUST
> ON GM.Customer_ID = CUST.Customer_ID
> AND GM.Record_Is_Valid = 'Y'
> AND GRP.Record_Is_Valid = 'Y'
> AND GRP.Group_Customer_ID = GM.Group_Customer_ID
> AND GM.Record_Is_Valid = 'Y'
>
> Query is executing longer than 2000 seconds. Any suggestion on this?
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>