how to troubleshoot slow query, so slow query!

[胡永亮/Bob <hu...@neusoft.com>]

hello everyone:

        My slow query code is "
        try (Ignite ignite = Ignition.start("config_poc.xml")) {
try (IgniteCache<Kc21Key, Kc21> cache = ignite.cache("Kc21Cache")) {

String querySql = "select count(*) FROm Kc21, \"Kc24Cache\".Kc24"
+ ", \"Ka06Cache\".Ka06, \"Kc60Cache\".Kc60 "
+ " WHERE kc21.akb020 = kc24.akb020"
+ " AND kc21.akc190 = kc24.akc190"
+ " AND kc24.akb020 = kc60.akb020"
+ " AND kc24.akc190 = kc60.akc190"
+ " AND kc24.aae072 = kc60.aae072"
+ " AND kc24.bka135 = kc60.bka135"
+ " AND COALESCE (kc24.bkc380, '0') = '0'"
+ " AND kc24.ake010 BETWEEN '2014-1-1' AND '2015-1-1'"
+ " AND kc21.akc193 = ka06.akc193";

SqlFieldsQuery qry = new SqlFieldsQuery(querySql);
try(QueryCursor<List<?>> cursor = cache.query(qry)){

for (List<?> row : cursor){
System.out.println(row.get(0));
}

}
} 
"

            This query takes time in Ignite: 03mins22s927ms, but it consumed 0.031s in oracle. 

            I created caches as the following code: "
            CacheJdbcPojoStoreFactory<K, V> pojoStoreFactory = new CacheJdbcPojoStoreFactory<>();
pojoStoreFactory
.setDataSourceFactory(new OracleDataSourceFactory());

// Configure cache store.
CacheConfiguration<K, V> cfg = CacheConfig.cache(cacheName,
pojoStoreFactory);

cfg.setMemoryMode(CacheMemoryMode.OFFHEAP_TIERED);
cfg.setAtomicityMode(CacheAtomicityMode.ATOMIC);
cfg.setCacheMode(CacheMode.PARTITIONED);

logger.info(">>> create cache:" + cacheName);
ignite.destroyCache(cacheName);
try (IgniteCache<K, V> cache = ignite.getOrCreateCache(cfg)) {
"

            My test environment is: 
            Ignite version: 1.6
            Topology snapshot [ver=11, servers=1, clients=1, CPUs=34, heap=5.5GB]
            kc21:9999 records, kc24:9999 records, kc60:9999 records, ka06: 103 records

        In code attachment file, StoreMain class is used to load data from oracle to Ignite.
        DataGrid class is used to test query sql.

        Thanks your reply.



            



Bob


---------------------------------------------------------------------------------------------------
Confidentiality Notice: The information contained in this e-mail and any accompanying attachment(s)
is intended only for the use of the intended recipient and may be confidential and/or privileged of
Neusoft Corporation, its subsidiaries and/or its affiliates. If any reader of this communication is
not the intended recipient, unauthorized use, forwarding, printing,  storing, disclosure or copying
is strictly prohibited, and may be unlawful.If you have received this communication in error,please
immediately notify the sender by return e-mail, and delete the original message and all copies from
your system. Thank you.
---------------------------------------------------------------------------------------------------

Re: how to troubleshoot slow query, so slow query!

[vkulichenko <va...@gmail.com>]

Hi Bob,

Please run EXPLAIN [1] and check the execution plan. Are there any full
scans implied? I would also recommend to use H2 console [2] for debugging.

[1] https://apacheignite.readme.io/docs/sql-queries#using-explain
[2] https://apacheignite.readme.io/docs/sql-queries#using-h2-debug-console

-Val



--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/how-to-troubleshoot-slow-query-so-slow-query-tp6322p6326.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.