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 <[email protected]>: > 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/ >
