Hi Andrew,
Thanks for the reply. Removing index did helped. But we got the same issue
in one more query. Could you let us know how to what could be the issue
here.
02:51:52.591 [http-/172.30.8.97:8443-27] WARN
org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing - Query
execution is too long [time=3098 ms, sql='SELECT
PGE__Z1.APPLICATION_DATE __C1_0,
PGE__Z1.CR_DECSN_BUSINESS_PG_SURR_ID __C1_1,
PGE__Z1.INCOMING_CRID __C1_2,
PGE__Z1.ORIGINAL_CRID __C1_3,
PGE__Z1.BUSINESS_NAME __C1_4,
PGE__Z1.SUPPRESSION_FLAG __C1_5,
PGE__Z1.EFFECTIVE_DT __C1_6,
PGE__Z1.EXPIRATION_DT __C1_7,
PGE__Z1.FINAL_CREDIT_CLASS __C1_8,
PGE__Z1.COUNTER __C1_9,
PGE__Z1.CHANNEL __C1_10,
PGE__Z1.TRANSACTION_TIMESTAMP __C1_11,
PGE__Z1.GAMING_DUP_COUNTER __C1_12,
PGE__Z1.GAMING_FLAG __C1_13,
PGE__Z1.PROMOTIONAL_CODE __C1_14,
PGE__Z1.CREDIT_CLASS_PORT_IN __C1_15,
PGE__Z1.CREDIT_CLASS_WOUT_PORT_IN __C1_16,
PGE__Z1.TAX_ID_ENCRYPT __C1_17,
PGE__Z1.BUSINESS_ADDRESS_LINE1 __C1_18,
PGE__Z1.BUSINESS_ZIP __C1_19,
PGE__Z1.BUSINESS_CITY __C1_20,
PGE__Z1.BUSINESS_STATE __C1_21
FROM "CustomIgniteCacheBusinessPg".CRDECISIONBUSINESSPGENTITY PGE__Z1
WHERE ((PGE__Z1.SUPPRESSION_FLAG = 'N') AND (PGE__Z1.EXPIRATION_DT >=
CURRENT_DATE())) AND (PGE__Z1.APPLICATION_DATE >= DATEADD('DAY', -30,
CURRENT_DATE()))', plan=
SELECT
PGE__Z1.APPLICATION_DATE AS __C1_0,
PGE__Z1.CR_DECSN_BUSINESS_PG_SURR_ID AS __C1_1,
PGE__Z1.INCOMING_CRID AS __C1_2,
PGE__Z1.ORIGINAL_CRID AS __C1_3,
PGE__Z1.BUSINESS_NAME AS __C1_4,
PGE__Z1.SUPPRESSION_FLAG AS __C1_5,
PGE__Z1.EFFECTIVE_DT AS __C1_6,
PGE__Z1.EXPIRATION_DT AS __C1_7,
PGE__Z1.FINAL_CREDIT_CLASS AS __C1_8,
PGE__Z1.COUNTER AS __C1_9,
PGE__Z1.CHANNEL AS __C1_10,
PGE__Z1.TRANSACTION_TIMESTAMP AS __C1_11,
PGE__Z1.GAMING_DUP_COUNTER AS __C1_12,
PGE__Z1.GAMING_FLAG AS __C1_13,
PGE__Z1.PROMOTIONAL_CODE AS __C1_14,
PGE__Z1.CREDIT_CLASS_PORT_IN AS __C1_15,
PGE__Z1.CREDIT_CLASS_WOUT_PORT_IN AS __C1_16,
PGE__Z1.TAX_ID_ENCRYPT AS __C1_17,
PGE__Z1.BUSINESS_ADDRESS_LINE1 AS __C1_18,
PGE__Z1.BUSINESS_ZIP AS __C1_19,
PGE__Z1.BUSINESS_CITY AS __C1_20,
PGE__Z1.BUSINESS_STATE AS __C1_21
FROM "CustomIgniteCacheBusinessPg".CRDECISIONBUSINESSPGENTITY PGE__Z1
/* "CustomIgniteCacheBusinessPg"."EXPIRATION_DT_idx": EXPIRATION_DT >=
CURRENT_DATE() */
WHERE ((PGE__Z1.SUPPRESSION_FLAG = 'N')
AND (PGE__Z1.EXPIRATION_DT >= CURRENT_DATE()))
AND (PGE__Z1.APPLICATION_DATE >= DATEADD('DAY', -30, CURRENT_DATE()))
, parameters=[]]
We have tried below things as part of performance tunning.
****************************************
consumerConfig = new CacheConfiguration<>(CONSUMER_CACHE);
consumerConfig.setCopyOnRead(false);
consumerConfig.setMemoryMode(CacheMemoryMode.OFFHEAP_TIERED);
//consumerConfig.setRebalanceMode(CacheRebalanceMode.ASYNC);
consumerConfig.setCacheMode(CacheMode.PARTITIONED); // Default.
consumerConfig.setCacheStoreFactory(FactoryBuilder.factoryOf(CacheStoreConsumer.class));
consumerConfig.setBackups(backUps);
consumerConfig.setRebalanceBatchSize(Integer.valueOf(environment.getProperty("cache.rebalancebathcsize"))
* Integer.valueOf(environment.getProperty("cache.rebalancebathcsize")));
//consumerConfig.setSnapshotableIndex(true);
consumerConfig.setRebalanceThrottle(Integer.valueOf(environment.getProperty("cache.rebalancethrottle")));
consumerConfig.setStartSize(Integer.valueOf(environment.getProperty("cache.consumer.initialSize")));
consumerConfig.setOffHeapMaxMemory(Integer.valueOf(environment.getProperty("cache.consumer.ignite.offHeapmemory")));
consumerConfig.setIndexedTypes(Long.class,
CRDecisionConsumerEntity.class);
consumerCache = ignite.getOrCreateCache(consumerConfig);
***********************************
is it ok to use the snapshot for the query indexc ?
I have attached the Entity we are using with index, could you please check.
Below is the query we are trying to execute.
String filterString = " WHERE SUPPRESSION_FLAG IN (" N ") and EXPIRATION_DT
>= CURRENT_DATE() and APPLICATION_DATE "
>= CURRENT_DATE() - 90;
SELECT APPLICATION_DATE, CR_DECSN_BUSINESS_SURR_ID, INCOMING_CRID,
ORIGINAL_CRID, BUSINESS_NAME, "
+ " SUPPRESSION_FLAG, EFFECTIVE_DT, EXPIRATION_DT,
FINAL_CREDIT_CLASS ,
COUNTER, CHANNEL, TRANSACTION_TIMESTAMP, GAMING_DUP_COUNTER, "
+ " GAMING_FLAG,PROMOTIONAL_CODE,CREDIT_CLASS_PORT_IN,
CREDIT_CLASS_WOUT_PORT_IN, TAX_ID_ENCRYPT, BUSINESS_ADDRESS_LINE1,"
+ " BUSINESS_ZIP, BUSINESS_CITY, BUSINESS_STATE from
\"" +
JwConstants.BUSINESS_CACHE_NAME
+ "\".CRDecisionBusinessEntity as be "
+ filterString
+ " UNION ALL
"+BUSINESS_WITH_PG_SELECT_FEILDS_QUERY+ " from \""
+ JwConstants.BUSINESS_PG_CACHE_NAME +
"\".CRDecisionBusinessPGEntity
as pge " + filterString;
Appreciate all your help
--
View this message in context:
http://apache-ignite-users.70518.x6.nabble.com/org-apache-ignite-internal-processors-query-h2-IgniteH2Indexing-Query-execution-is-too-long-tp12182p12281.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
