Hi,
1. You may want to make Ignite use index on APPLICATION_DATE or even
composite index of two fields: EXPIRATION_DT, APPLICATION_DATE.
Ignite does not support HINTs in 1.x version, but it will be added in 2.0.
2. I see no query plan for second query. Try to run in from console with
explain command.
BTW, I see no Ignite issues here, these are common sql server problem to
make query run with appropriate index.
On Thu, Apr 27, 2017 at 9:18 AM, abhijitprusty <abhijitpru...@fico.com>
wrote:
> 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.
>
--
Best regards,
Andrey V. Mashenkov
