Hi , These are the query we use for the cache, could you please help to validate, if it requires parallelism.
SqlFieldsQuery consumerGaminigQuery = new SqlFieldsQuery(CONSUMER_SELECT_FEILDS_QUERY + " from CRDecisionConsumerEntity where FIRST_NAME = ? UNION " + CONSUMER_SELECT_FEILDS_QUERY + " from CRDecisionConsumerEntity where LAST_NAME = ? + " UNION " + CONSUMER_SELECT_FEILDS_QUERY + " from CRDecisionConsumerEntity where BIRTH_DT = ? " ).setArgs(jwr.fname, jwr.lname, jwr.dob); From: Andrey Mashenkov [mailto:andrey.mashen...@gmail.com] Sent: Monday, April 24, 2017 3:54 PM To: user@ignite.apache.org Subject: Re: org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing - Query execution is too long Hi Abhijit, Yes, three is a limitation: all PARTITIONED caches participating in same query should have same queryParallelismLevel. You can set it viaCacheConfiguration.setQueryParallelism(..) or XML property queryParallelism in cache bean configuration. On Mon, Apr 24, 2017 at 1:18 PM, Abhijit Prusty <abhijitpru...@fico.com<mailto:abhijitpru...@fico.com>> wrote: Hi Andrey, So, is there a limitation on the threads for query parallelism . Could you pass any sample configuration ? Thanks, Abhijit From: Andrey Mashenkov [mailto:andrey.mashen...@gmail.com<mailto:andrey.mashen...@gmail.com>] Sent: Monday, April 24, 2017 3:17 PM To: user@ignite.apache.org<mailto:user@ignite.apache.org> Subject: Re: org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing - Query execution is too long Hi, Looks like your query use unapropriate index "CustomIgniteCacheConsumer"."SUPPRESSION_FLAG_idx": You can try to add index with better selectivity. It is possible you already have it and issue is caused by H2 query planner. Index hint support will be added in ignite-2.0 that come very soon. Also you can try to utilize more CPU per node for query [1] that available from 1.9 but it is not working with OFFHEAP due to bug. [1] https://apacheignite.readme.io/docs/sql-performance-and-debugging#query-parallelism On Mon, Apr 24, 2017 at 12:31 PM, abhijitprusty <abhijitpru...@fico.com<mailto:abhijitpru...@fico.com>> wrote: Could any one please help me with the bewlo error. This is my ignite configuration ********************************************** consumerConfig = new CacheConfiguration<>(CONSUMER_CACHE); consumerConfig.setMemoryMode(CacheMemoryMode.OFFHEAP_TIERED); consumerConfig.setRebalanceMode(CacheRebalanceMode.ASYNC); consumerConfig.setCacheMode(CacheMode.PARTITIONED); // Default. consumerConfig.setCacheStoreFactory(FactoryBuilder.factoryOf(CacheStoreConsumer.class)); consumerConfig.setBackups(backUps); consumerConfig.setStartSize(Integer.valueOf(environment.getProperty("cache.consumer.initialSize"))); consumerConfig.setOffHeapMaxMemory(Integer.valueOf(environment.getProperty("cache.consumer.ignite.offHeapmemory"))); // consumerConfig.setReadThrough(true); // consumerConfig.setWriteThrough(true); consumerConfig.setIndexedTypes(Long.class, CRDecisionConsumerEntity.class); consumerCache = ignite.getOrCreateCache(consumerConfig); ********************************************** 09:06:33.371 [http-/172.30.72.8:8443-30] WARN org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing - Query execution is too long [time=3239 ms, sql='SELECT __Z0.INCOMING_CRID __C0_0, __Z0.ORIGINAL_CRID __C0_1, __Z0.SSN_ENCRYPT __C0_2, __Z0.FIRST_NAME __C0_3, __Z0.LAST_NAME __C0_4, __Z0.ADDRESS_LINE1 __C0_5, __Z0.CITY __C0_6, __Z0.STATE __C0_7, __Z0.ZIP __C0_8, __Z0.BIRTH_DT __C0_9, __Z0.FINAL_CREDIT_CLASS __C0_10, __Z0.CREDIT_CLASS_PORT_IN __C0_11, __Z0.CREDIT_CLASS_WOUT_PORT_IN __C0_12, __Z0.COUNTER __C0_13, __Z0.CHANNEL __C0_14, __Z0.EFFECTIVE_DT __C0_15, __Z0.EXPIRATION_DT __C0_16, __Z0.TRANSACTION_TIMESTAMP __C0_17, __Z0.GAMING_DUP_COUNTER __C0_18, __Z0.GAMING_FLAG __C0_19, __Z0.APPLICATION_DATE __C0_20, __Z0.SUPPRESSION_FLAG __C0_21, __Z0.FULL_NAME __C0_22, __Z0.CR_DECSN_CONSUMER_SURR_ID __C0_23, __Z0.INDV_ID __C0_24, __Z0.PROMOTIONAL_CODE __C0_25 FROM "CustomIgniteCacheConsumer".CRDECISIONCONSUMERENTITY __Z0 WHERE (__Z0.APPLICATION_DATE >= DATEADD('DAY', -30, CURRENT_DATE())) AND (((__Z0.FIRST_NAME = ?1) AND (__Z0.SUPPRESSION_FLAG = 'N')) AND (__Z0.EXPIRATION_DT >= CURRENT_DATE()))', plan= SELECT __Z0.INCOMING_CRID AS __C0_0, __Z0.ORIGINAL_CRID AS __C0_1, __Z0.SSN_ENCRYPT AS __C0_2, __Z0.FIRST_NAME AS __C0_3, __Z0.LAST_NAME AS __C0_4, __Z0.ADDRESS_LINE1 AS __C0_5, __Z0.CITY AS __C0_6, __Z0.STATE AS __C0_7, __Z0.ZIP AS __C0_8, __Z0.BIRTH_DT AS __C0_9, __Z0.FINAL_CREDIT_CLASS AS __C0_10, __Z0.CREDIT_CLASS_PORT_IN AS __C0_11, __Z0.CREDIT_CLASS_WOUT_PORT_IN AS __C0_12, __Z0.COUNTER AS __C0_13, __Z0.CHANNEL AS __C0_14, __Z0.EFFECTIVE_DT AS __C0_15, __Z0.EXPIRATION_DT AS __C0_16, __Z0.TRANSACTION_TIMESTAMP AS __C0_17, __Z0.GAMING_DUP_COUNTER AS __C0_18, __Z0.GAMING_FLAG AS __C0_19, __Z0.APPLICATION_DATE AS __C0_20, __Z0.SUPPRESSION_FLAG AS __C0_21, __Z0.FULL_NAME AS __C0_22, __Z0.CR_DECSN_CONSUMER_SURR_ID AS __C0_23, __Z0.INDV_ID AS __C0_24, __Z0.PROMOTIONAL_CODE AS __C0_25 FROM "CustomIgniteCacheConsumer".CRDECISIONCONSUMERENTITY __Z0 /* "CustomIgniteCacheConsumer"."SUPPRESSION_FLAG_idx": SUPPRESSION_FLAG = 'N' */ WHERE (__Z0.APPLICATION_DATE >= DATEADD('DAY', -30, CURRENT_DATE())) AND (((__Z0.FIRST_NAME = ?1) AND (__Z0.SUPPRESSION_FLAG = 'N')) AND (__Z0.EXPIRATION_DT >= CURRENT_DATE())) , parameters=[JWHGDLQI]] -- 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-tp12182.html Sent from the Apache Ignite Users mailing list archive at Nabble.com. -- Best regards, Andrey V. Mashenkov This email and any files transmitted with it are confidential, proprietary and intended solely for the individual or entity to whom they are addressed. If you have received this email in error please delete it immediately. -- Best regards, Andrey V. Mashenkov This email and any files transmitted with it are confidential, proprietary and intended solely for the individual or entity to whom they are addressed. If you have received this email in error please delete it immediately.