Mailing-List: contact user-help@ignite.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user@ignite.apache.org
MIME-Version: 1.0
In-Reply-To: 
 <CALOGAoy-MZH-bGg8uMB3Rfa92+gv6CGf2qRfF=Hh2eSK=VObHA@mail.gmail.com>
References: <1459185767838-3718.post@n6.nabble.com>
	<CALOGAowdHs5pJd7N-AYVmGDKZgoSGDhWO9e6wVztB-1d5b+Gqg@mail.gmail.com>
	<1459252611591-3740.post@n6.nabble.com>
	<1459332364043-3771.post@n6.nabble.com>
	<CALOGAoxgz36naFS45ei0ieG+QmsVyu_dDPN=1uJ0TLrwfj+Cpw@mail.gmail.com>
	<1459342771493-3779.post@n6.nabble.com>
	<1459779824263-3902.post@n6.nabble.com>
	<CALOGAozJzFM5m3m7Us6FapZXAQ0his+FF6Uub+D_574rYoSeYw@mail.gmail.com>
	<1459834181100-3920.post@n6.nabble.com>
	<CALOGAozV+8X-wJJGT8+CPPA5C=vGJpnAhgxNjFzn8_3SeLRv6A@mail.gmail.com>
	<1460384264690-4064.post@n6.nabble.com>
	<CALOGAoy-MZH-bGg8uMB3Rfa92+gv6CGf2qRfF=Hh2eSK=VObHA@mail.gmail.com>
Date: Wed, 13 Apr 2016 17:48:43 +0300
Message-ID: 
 <CALOGAoyayNLAjRy3pEEz_4Juptyd44WuYjn-OdLRZ71XNz-ZZQ@mail.gmail.com>
Subject: Re: Host Arrays in C++ API
From: Igor Sapego <isapego@gridgain.com>
To: user@ignite.apache.org
Content-Type: multipart/alternative; boundary=94eb2c032dcccef35805305edfaa

--94eb2c032dcccef35805305edfaa
Content-Type: text/plain; charset=UTF-8

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 <isapego@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.ap@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.
>>
>
>

--94eb2c032dcccef35805305edfaa
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Sergi,<div><br></div><div>Can you please take a look?</div=
><div><br></div><div>Here is the query:</div><div><div>SELECT sid_per_id, s=
id_mah_id, sid_itm_id, sid_prm_id, sid_cha_code, sid_service, sid_itm_dist=
=C2=A0</div><div>FROM SHOP_ITEM_BITMAP_CACHE.ShopItemBitmap as bitmap=C2=A0=
</div><div>=C2=A0 =C2=A0 JOIN PROD_HIER_CACHE.ProdHier as prod ON prod.GHD_=
LEV_6 =3D bitmap.SID_ITM_ID AND prod.GHD_HIE_ID =3D 0 AND prod.ghd_lev_2_de=
sc =3D &#39;WINE&#39;=C2=A0</div><div>=C2=A0 =C2=A0 JOIN MRKT_POD_ACV_CACHE=
.MarketPodAcv as mkt ON mkt.CHLD_MRKT_KEY =3D bitmap.SID_MAH_ID and mkt.MRK=
T_KEY =3D 4378038 AND mkt.prd_id =3D 712=C2=A0</div><div>WHERE bitmap.SID_P=
ER_ID =3D 712;</div></div><div><br></div><div>Here is the explain output:</=
div><div><div>[[SELECT DISTINCT</div><div>=C2=A0 =C2=A0 SID_PER_ID AS __C0,=
</div><div>=C2=A0 =C2=A0 SID_MAH_ID AS __C1,</div><div>=C2=A0 =C2=A0 SID_IT=
M_ID AS __C2,</div><div>=C2=A0 =C2=A0 SID_PRM_ID AS __C3,</div><div>=C2=A0 =
=C2=A0 SID_CHA_CODE AS __C4,</div><div>=C2=A0 =C2=A0 SID_SERVICE AS __C5,</=
div><div>=C2=A0 =C2=A0 SID_ITM_DIST AS __C6</div><div>FROM PROD_HIER_CACHE.=
PRODHIER PROD</div><div>=C2=A0 =C2=A0 /* PROD_HIER_CACHE.&quot;ghd_hie_id_a=
sc_ghd_lev_2_desc_asc_idx&quot;: GHD_HIE_ID =3D 0</div><div>=C2=A0 =C2=A0 =
=C2=A0 =C2=A0 AND GHD_LEV_2_DESC =3D &#39;WINE&#39;</div><div>=C2=A0 =C2=A0=
 =C2=A0*/</div><div>=C2=A0 =C2=A0 /* WHERE (PROD.GHD_HIE_ID =3D 0)</div><di=
v>=C2=A0 =C2=A0 =C2=A0 =C2=A0 AND (PROD.GHD_LEV_2_DESC =3D &#39;WINE&#39;)<=
/div><div>=C2=A0 =C2=A0 */</div><div>INNER JOIN SHOP_ITEM_BITMAP_CACHE.SHOP=
ITEMBITMAP</div><div>=C2=A0 =C2=A0 /* SHOP_ITEM_BITMAP_CACHE.&quot;sid_itm_=
id_asc_idx&quot;: SID_ITM_ID =3D PROD.GHD_LEV_6 */</div><div>=C2=A0 =C2=A0 =
ON 1=3D1</div><div>=C2=A0 =C2=A0 /* WHERE (SHOPITEMBITMAP.SID_PER_ID =3D 71=
2)</div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 AND (PROD.GHD_LEV_6 =3D SHOPITEMBI=
TMAP.SID_ITM_ID)</div><div>=C2=A0 =C2=A0 */</div><div>INNER JOIN MARKET_POD=
_ACV_CACHE.MARKETPODACV MKT</div><div>=C2=A0 =C2=A0 /* MARKET_POD_ACV_CACHE=
.&quot;mrkt_key_asc_prd_id_asc_chld_mrkt_key_asc_idx&quot;: PRD_ID =3D 712<=
/div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 AND MRKT_KEY =3D 4378038</div><div>=
=C2=A0 =C2=A0 =C2=A0 =C2=A0 AND CHLD_MRKT_KEY =3D SHOPITEMBITMAP.SID_MAH_ID=
</div><div>=C2=A0 =C2=A0 =C2=A0*/</div><div>=C2=A0 =C2=A0 ON 1=3D1</div><di=
v>WHERE (SHOPITEMBITMAP.SID_PER_ID =3D 712)</div><div>=C2=A0 =C2=A0 AND (((=
MKT.PRD_ID =3D 712)</div><div>=C2=A0 =C2=A0 AND ((MKT.MRKT_KEY =3D 4378038)=
</div><div>=C2=A0 =C2=A0 AND (MKT.CHLD_MRKT_KEY =3D SHOPITEMBITMAP.SID_MAH_=
ID)))</div><div>=C2=A0 =C2=A0 AND ((PROD.GHD_LEV_6 =3D SHOPITEMBITMAP.SID_I=
TM_ID)</div><div>=C2=A0 =C2=A0 AND ((PROD.GHD_HIE_ID =3D 0)</div><div>=C2=
=A0 =C2=A0 AND (PROD.GHD_LEV_2_DESC =3D &#39;WINE&#39;))))], [SELECT DISTIN=
CT</div><div>=C2=A0 =C2=A0 __C0 AS SID_PER_ID,</div><div>=C2=A0 =C2=A0 __C1=
 AS SID_MAH_ID,</div><div>=C2=A0 =C2=A0 __C2 AS SID_ITM_ID,</div><div>=C2=
=A0 =C2=A0 __C3 AS SID_PRM_ID,</div><div>=C2=A0 =C2=A0 __C4 AS SID_CHA_CODE=
,</div><div>=C2=A0 =C2=A0 __C5 AS SID_SERVICE,</div><div>=C2=A0 =C2=A0 __C6=
 AS SID_ITM_DIST</div><div>FROM PUBLIC.__T0</div><div>=C2=A0 =C2=A0 /* SHOP=
_ITEM_BITMAP_CACHE.&quot;merge_scan&quot; */]]</div></div><div><br></div><d=
iv>The first thing I could see is that there is no index for &quot;sid_per_=
id&quot; and the second thing is that &quot;merge_scan&quot; is being used.=
</div><div>What is your thoughts?</div><div><br></div></div><div class=3D"g=
mail_extra"><br clear=3D"all"><div><div class=3D"gmail_signature"><div dir=
=3D"ltr"><div>Best Regards,</div><div>Igor</div></div></div></div>
<br><div class=3D"gmail_quote">On Tue, Apr 12, 2016 at 2:11 PM, Igor Sapego=
 <span dir=3D"ltr">&lt;<a href=3D"mailto:isapego@gridgain.com" target=3D"_b=
lank">isapego@gridgain.com</a>&gt;</span> wrote:<br><blockquote class=3D"gm=
ail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-le=
ft:1ex"><div dir=3D"ltr">Arthi,<div><br></div><div>How much time does it ta=
ke? What is the speed of fetching in rows</div><div>per second?</div><div><=
br></div><div>Guys, can someone who is more familiar with Ignite&#39;s SQL =
queries</div><div>take a look?</div></div><div class=3D"gmail_extra"><br cl=
ear=3D"all"><div><div><div dir=3D"ltr"><div>Best Regards,</div><div>Igor</d=
iv></div></div></div><div><div class=3D"h5">
<br><div class=3D"gmail_quote">On Mon, Apr 11, 2016 at 5:17 PM, arthi <span=
 dir=3D"ltr">&lt;<a href=3D"mailto:Arthi.Kasturirangan.ap@nielsen.com" targ=
et=3D"_blank">Arthi.Kasturirangan.ap@nielsen.com</a>&gt;</span> wrote:<br><=
blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px=
 #ccc solid;padding-left:1ex">Hi Igor,<br>
<br>
the SQL query for getNext() takes a whole lot of time when the results<br>
returned is more than 900,000 both using Java and C++ API. I have tried a<b=
r>
number of configurations with indexes, but just dont get to speed these up.=
<br>
<br>
thanks,<br>
Arthi<br>
<br>
<br>
<br>
--<br>
View this message in context: <a href=3D"http://apache-ignite-users.70518.x=
6.nabble.com/Host-Arrays-in-C-API-tp3707p4064.html" rel=3D"noreferrer" targ=
et=3D"_blank">http://apache-ignite-users.70518.x6.nabble.com/Host-Arrays-in=
-C-API-tp3707p4064.html</a><br>
<div><div>Sent from the Apache Ignite Users mailing list archive at Nabble.=
com.<br>
</div></div></blockquote></div><br></div></div></div>
</blockquote></div><br></div>

--94eb2c032dcccef35805305edfaa--