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 2019/04/05 18:35:49 UTC
Ignite Performance
I have 250,000 records for each cache store and when I tried to join they are
pretty slow.
Is annotation -> @affinityKey, @SqlQueryField works in java version? I am
using JDK1.8 version AND NOT Spring version.
Can someone help how to use @affinitykey in group for below example in
'PARTIONED' without creating too much index. I am using REPLICATE mode now.
The example in ignite website is not clear with just two tables. What are
various recommendation to get good response from query execution.
Cache1
-------
package com.test.config
public class Account implements Serializable {
/** */
private static final long serialVersionUID = 0L;
/** Value for dwId. */
private long dwId;
/** Value for accountNumber. */
private Long accountNumber;
/** Value for accountType. */
private String accountType;
}
-------
package com.test.config
public class AccountCache {
public static CacheConfiguration createAccountCache() throws Exception {
CacheConfiguration ccfg = new CacheConfiguration();
ccfg.setName(cacheName);
ccfg.setCacheMode(CacheMode.REPLICATED);
cacheStoreFactory.setTypes(jdbcTypeAccount("Account");
ccfg.setCacheStoreFactory(cacheStoreFactory);
ArrayList<QueryEntity> qryEntities = new ArrayList<>();
QueryEntity qryEntity = new QueryEntity();
qryEntity.setKeyType("java.lang.Long");
qryEntity.setValueType("com.test.Account");
qryEntity.setTableName("Account");
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("accountNumber", "java.lang.Long");
fields.put("accountType", "java.lang.String");
}
**/
private static JdbcType jdbcTypeAccount(String cacheName) {
JdbcType type = new JdbcType();
type.setCacheName(cacheName);
type.setKeyType(Long.class);
type.setValueType("com.test.Account");
type.setDatabaseSchema("dbo");
type.setDatabaseTable("Account");
type.setKeyFields(new JdbcTypeField(Types.BIGINT, "DW_Id",
long.class, "dwId"));
type.setValueFields(
new JdbcTypeField(Types.BIGINT, "Account_Number",
Long.class, "accountNumber"),
new JdbcTypeField(Types.VARCHAR, "Account_Type",
String.class, "accountType"),
);
return type;
}
}
========
Cache 2
package com.test.config
public class AccountAddress implements Serializable {
/** */
private static final long serialVersionUID = 0L;
/** Value for dwId. */
private long dwId;
/** Value for accountNumber. */
private Long accountNumber;
/** Value for accountType. */
private String accountType;
/** Value for addressId. */
private Long addressId;
}
-----
package com.test.config
public class AccountAddressCache {
public static CacheConfiguration createAccountAddressCache() throws
Exception {
CacheConfiguration ccfg = new CacheConfiguration();
ccfg.setName(cacheName);
ccfg.setCacheMode(CacheMode.REPLICATED);
cacheStoreFactory.setTypes(jdbcTypeAccountAddress("AccountAddress");
ccfg.setCacheStoreFactory(cacheStoreFactory);
ArrayList<QueryEntity> qryEntities = new ArrayList<>();
QueryEntity qryEntity = new QueryEntity();
qryEntity.setKeyType("java.lang.Long");
qryEntity.setValueType("com.test.AccountAddress");
qryEntity.setTableName("Account_Address");
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("accountNumber", "java.lang.Long");
fields.put("accountType", "java.lang.String");
qryEntity.setFields(fields);
ArrayList<QueryIndex> indexes = new ArrayList<>();
QueryIndex index = new QueryIndex();
index.setName("NonClustered_Index_AC_ID");
index.setIndexType(QueryIndexType.SORTED);
LinkedHashMap<String, Boolean> indFlds = new LinkedHashMap<>();
indFlds.put("dwId", true);
indFlds.put("accountNumber", true);
indexes.add(index);
qryEntity.setIndexes(indexes);
qryEntities.add(qryEntity);
//ccfg.setIndexedTypes(Long.class,Address.class);
ccfg.setQueryEntities(qryEntities);
return ccfg;
}
/**
* Create JDBC type for "jdbcTypeAccountAddress".
*
* @param cacheName Cache name.
* @return Configured JDBC type.
**/
private static JdbcType jdbcTypeAccountAddress(String cacheName) {
JdbcType type = new JdbcType();
type.setCacheName(cacheName);
type.setKeyType(Long.class);
type.setValueType("com.test.AccountAddress");
type.setDatabaseSchema("dbo");
type.setDatabaseTable("Account_Address");
type.setKeyFields(new JdbcTypeField(Types.BIGINT, "DW_Id",
long.class, "dwId"));
type.setValueFields(
new JdbcTypeField(Types.BIGINT, "Account_Number",
Long.class, "accountNumber"),
new JdbcTypeField(Types.VARCHAR, "Account_Type",
String.class, "accountType"),
new JdbcTypeField(Types.BIGINT, "Address_ID", Long.class,
"addressId"),
new JdbcTypeField(Types.VARCHAR, "Address_Type",
String.class, "addressType"),
);
return type;
}
}
======================================
selet * from AccountAddress a, AccountAddress b
where a.accountNumber=b.accountNumber
and a.accountType=b.accountType
The above query returns results in .08 seconds in ms-sql against 1.2 seconds
in ignite.
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: Ignite Performance
Posted by Ilya Kasnacheev <il...@gmail.com>.
Hello!
Can you provide EXPLAIN output (all rows) for this query?
I don't see @AffinityKeyMapped in your examples and doubt it will work with
QueryEntities anyway. Please try to use CacheKeyConfiguration instead to
specify affinity fields. However, I'm not sure if you can use two filelds
as affinity this way (or any other for that matter).
Regards,
--
Ilya Kasnacheev
пт, 5 апр. 2019 г. в 21:59, Skollur <Sk...@idbny.com>:
> I have 250,000 records for each cache store and when I tried to join they
> are
> pretty slow.
> Is annotation -> @affinityKey, @SqlQueryField works in java version? I am
> using JDK1.8 version AND NOT Spring version.
>
> Can someone help how to use @affinitykey in group for below example in
> 'PARTIONED' without creating too much index. I am using REPLICATE mode now.
> The example in ignite website is not clear with just two tables. What are
> various recommendation to get good response from query execution.
>
>
> Cache1
> -------
>
> package com.test.config
> public class Account implements Serializable {
> /** */
> private static final long serialVersionUID = 0L;
>
> /** Value for dwId. */
> private long dwId;
>
> /** Value for accountNumber. */
> private Long accountNumber;
>
> /** Value for accountType. */
> private String accountType;
> }
>
> -------
> package com.test.config
> public class AccountCache {
>
> public static CacheConfiguration createAccountCache() throws
> Exception {
> CacheConfiguration ccfg = new CacheConfiguration();
> ccfg.setName(cacheName);
> ccfg.setCacheMode(CacheMode.REPLICATED);
> cacheStoreFactory.setTypes(jdbcTypeAccount("Account");
> ccfg.setCacheStoreFactory(cacheStoreFactory);
>
> ArrayList<QueryEntity> qryEntities = new ArrayList<>();
> QueryEntity qryEntity = new QueryEntity();
> qryEntity.setKeyType("java.lang.Long");
> qryEntity.setValueType("com.test.Account");
> qryEntity.setTableName("Account");
> 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("accountNumber", "java.lang.Long");
> fields.put("accountType", "java.lang.String");
> }
>
>
> **/
> private static JdbcType jdbcTypeAccount(String cacheName) {
> JdbcType type = new JdbcType();
>
> type.setCacheName(cacheName);
> type.setKeyType(Long.class);
> type.setValueType("com.test.Account");
> type.setDatabaseSchema("dbo");
> type.setDatabaseTable("Account");
> type.setKeyFields(new JdbcTypeField(Types.BIGINT, "DW_Id",
> long.class, "dwId"));
> type.setValueFields(
> new JdbcTypeField(Types.BIGINT, "Account_Number",
> Long.class, "accountNumber"),
> new JdbcTypeField(Types.VARCHAR, "Account_Type",
> String.class, "accountType"),
> );
>
> return type;
> }
> }
> ========
> Cache 2
>
> package com.test.config
> public class AccountAddress implements Serializable {
> /** */
> private static final long serialVersionUID = 0L;
>
> /** Value for dwId. */
> private long dwId;
>
> /** Value for accountNumber. */
> private Long accountNumber;
>
> /** Value for accountType. */
> private String accountType;
>
> /** Value for addressId. */
> private Long addressId;
> }
>
> -----
> package com.test.config
> public class AccountAddressCache {
>
> public static CacheConfiguration createAccountAddressCache() throws
> Exception {
> CacheConfiguration ccfg = new CacheConfiguration();
> ccfg.setName(cacheName);
> ccfg.setCacheMode(CacheMode.REPLICATED);
>
> cacheStoreFactory.setTypes(jdbcTypeAccountAddress("AccountAddress");
> ccfg.setCacheStoreFactory(cacheStoreFactory);
>
> ArrayList<QueryEntity> qryEntities = new ArrayList<>();
>
> QueryEntity qryEntity = new QueryEntity();
> qryEntity.setKeyType("java.lang.Long");
> qryEntity.setValueType("com.test.AccountAddress");
> qryEntity.setTableName("Account_Address");
> 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("accountNumber", "java.lang.Long");
> fields.put("accountType", "java.lang.String");
>
> qryEntity.setFields(fields);
> ArrayList<QueryIndex> indexes = new ArrayList<>();
> QueryIndex index = new QueryIndex();
> index.setName("NonClustered_Index_AC_ID");
> index.setIndexType(QueryIndexType.SORTED);
> LinkedHashMap<String, Boolean> indFlds = new LinkedHashMap<>();
> indFlds.put("dwId", true);
> indFlds.put("accountNumber", true);
> indexes.add(index);
> qryEntity.setIndexes(indexes);
>
> qryEntities.add(qryEntity);
> //ccfg.setIndexedTypes(Long.class,Address.class);
> ccfg.setQueryEntities(qryEntities);
>
> return ccfg;
> }
>
> /**
> * Create JDBC type for "jdbcTypeAccountAddress".
> *
> * @param cacheName Cache name.
> * @return Configured JDBC type.
> **/
> private static JdbcType jdbcTypeAccountAddress(String cacheName) {
> JdbcType type = new JdbcType();
>
> type.setCacheName(cacheName);
> type.setKeyType(Long.class);
> type.setValueType("com.test.AccountAddress");
> type.setDatabaseSchema("dbo");
> type.setDatabaseTable("Account_Address");
> type.setKeyFields(new JdbcTypeField(Types.BIGINT, "DW_Id",
> long.class, "dwId"));
> type.setValueFields(
> new JdbcTypeField(Types.BIGINT, "Account_Number",
> Long.class, "accountNumber"),
> new JdbcTypeField(Types.VARCHAR, "Account_Type",
> String.class, "accountType"),
> new JdbcTypeField(Types.BIGINT, "Address_ID", Long.class,
> "addressId"),
> new JdbcTypeField(Types.VARCHAR, "Address_Type",
> String.class, "addressType"),
> );
>
> return type;
> }
>
> }
> ======================================
> selet * from AccountAddress a, AccountAddress b
> where a.accountNumber=b.accountNumber
> and a.accountType=b.accountType
>
> The above query returns results in .08 seconds in ms-sql against 1.2
> seconds
> in ignite.
>
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>