Sergi,
Can you please take a look?
Here is the query:
SELECT sid_per_id, sid_mah_id, sid_itm_id, sid_prm_id, sid_cha_code,
sid_service, sid_itm_dist
FROM SHOP_ITEM_BITMAP_CACHE.ShopItemBitmap as bitmap
JOIN PROD_HIER_CACHE.ProdHier as prod ON prod.GHD_LEV_6 =
bitmap.SID_ITM_ID AND prod.GHD_HIE_ID = 0 AND prod.ghd_lev_2_desc = 'WINE'
JOIN MRKT_POD_ACV_CACHE.MarketPodAcv as mkt ON mkt.CHLD_MRKT_KEY =
bitmap.SID_MAH_ID and mkt.MRKT_KEY = 4378038 AND mkt.prd_id = 712
WHERE bitmap.SID_PER_ID = 712;
Here is the explain output:
[[SELECT DISTINCT
SID_PER_ID AS __C0,
SID_MAH_ID AS __C1,
SID_ITM_ID AS __C2,
SID_PRM_ID AS __C3,
SID_CHA_CODE AS __C4,
SID_SERVICE AS __C5,
SID_ITM_DIST AS __C6
FROM PROD_HIER_CACHE.PRODHIER PROD
/* PROD_HIER_CACHE."ghd_hie_id_asc_ghd_lev_2_desc_asc_idx": GHD_HIE_ID
= 0
AND GHD_LEV_2_DESC = 'WINE'
*/
/* WHERE (PROD.GHD_HIE_ID = 0)
AND (PROD.GHD_LEV_2_DESC = 'WINE')
*/
INNER JOIN SHOP_ITEM_BITMAP_CACHE.SHOPITEMBITMAP
/* SHOP_ITEM_BITMAP_CACHE."sid_itm_id_asc_idx": SID_ITM_ID =
PROD.GHD_LEV_6 */
ON 1=1
/* WHERE (SHOPITEMBITMAP.SID_PER_ID = 712)
AND (PROD.GHD_LEV_6 = SHOPITEMBITMAP.SID_ITM_ID)
*/
INNER JOIN MARKET_POD_ACV_CACHE.MARKETPODACV MKT
/*
MARKET_POD_ACV_CACHE."mrkt_key_asc_prd_id_asc_chld_mrkt_key_asc_idx":
PRD_ID = 712
AND MRKT_KEY = 4378038
AND CHLD_MRKT_KEY = SHOPITEMBITMAP.SID_MAH_ID
*/
ON 1=1
WHERE (SHOPITEMBITMAP.SID_PER_ID = 712)
AND (((MKT.PRD_ID = 712)
AND ((MKT.MRKT_KEY = 4378038)
AND (MKT.CHLD_MRKT_KEY = SHOPITEMBITMAP.SID_MAH_ID)))
AND ((PROD.GHD_LEV_6 = SHOPITEMBITMAP.SID_ITM_ID)
AND ((PROD.GHD_HIE_ID = 0)
AND (PROD.GHD_LEV_2_DESC = 'WINE'))))], [SELECT DISTINCT
__C0 AS SID_PER_ID,
__C1 AS SID_MAH_ID,
__C2 AS SID_ITM_ID,
__C3 AS SID_PRM_ID,
__C4 AS SID_CHA_CODE,
__C5 AS SID_SERVICE,
__C6 AS SID_ITM_DIST
FROM PUBLIC.__T0
/* SHOP_ITEM_BITMAP_CACHE."merge_scan" */]]
The first thing I could see is that there is no index for "sid_per_id" and
the second thing is that "merge_scan" is being used.
What is your thoughts?
Best Regards,
Igor
On Tue, Apr 12, 2016 at 2:11 PM, Igor Sapego <isap...@gridgain.com> wrote:
> Arthi,
>
> How much time does it take? What is the speed of fetching in rows
> per second?
>
> Guys, can someone who is more familiar with Ignite's SQL queries
> take a look?
>
> Best Regards,
> Igor
>
> On Mon, Apr 11, 2016 at 5:17 PM, arthi <arthi.kasturirangan...@nielsen.com
> > wrote:
>
>> Hi Igor,
>>
>> the SQL query for getNext() takes a whole lot of time when the results
>> returned is more than 900,000 both using Java and C++ API. I have tried a
>> number of configurations with indexes, but just dont get to speed these
>> up.
>>
>> thanks,
>> Arthi
>>
>>
>>
>> --
>> View this message in context:
>> http://apache-ignite-users.70518.x6.nabble.com/Host-Arrays-in-C-API-tp3707p4064.html
>> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>>
>
>
