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/
