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/
