Return-Path: X-Original-To: archive-asf-public-internal@cust-asf2.ponee.io Delivered-To: archive-asf-public-internal@cust-asf2.ponee.io Received: from cust-asf.ponee.io (cust-asf.ponee.io [163.172.22.183]) by cust-asf2.ponee.io (Postfix) with ESMTP id 13A88200D4E for ; Thu, 7 Dec 2017 17:49:48 +0100 (CET) Received: by cust-asf.ponee.io (Postfix) id 121A1160C1E; Thu, 7 Dec 2017 16:49:48 +0000 (UTC) Delivered-To: archive-asf-public@cust-asf.ponee.io Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by cust-asf.ponee.io (Postfix) with SMTP id D8F50160C08 for ; Thu, 7 Dec 2017 17:49:46 +0100 (CET) Received: (qmail 26875 invoked by uid 500); 7 Dec 2017 16:49:45 -0000 Mailing-List: contact user-help@ignite.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: user@ignite.apache.org Delivered-To: mailing list user@ignite.apache.org Received: (qmail 26866 invoked by uid 99); 7 Dec 2017 16:49:45 -0000 Received: from mail-relay.apache.org (HELO mail-relay.apache.org) (140.211.11.15) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 07 Dec 2017 16:49:45 +0000 Received: from mail-qt0-f171.google.com (mail-qt0-f171.google.com [209.85.216.171]) by mail-relay.apache.org (ASF Mail Server at mail-relay.apache.org) with ESMTPSA id 7FA601A0019 for ; Thu, 7 Dec 2017 16:49:45 +0000 (UTC) Received: by mail-qt0-f171.google.com with SMTP id f2so19171232qtj.4 for ; Thu, 07 Dec 2017 08:49:45 -0800 (PST) X-Gm-Message-State: AKGB3mK7mgU0X4YHh00DFtXwPLbt7wRlskul8EPCjfi6k6FCID543A2w NudG54K/hPBwsFGie+TO87OMOToS3Z1e6TTLiqL22Q== X-Google-Smtp-Source: AGs4zMYpYzGMSVip7bwXpWczTdmt4H5lFadoKsPClLUb6+rwQyi53EMfD6NutpIMTWUS7uXIAgGnJhTvfImn8uIxYTs= X-Received: by 10.55.148.70 with SMTP id w67mr31262470qkd.235.1512665384595; Thu, 07 Dec 2017 08:49:44 -0800 (PST) MIME-Version: 1.0 Received: by 10.237.59.184 with HTTP; Thu, 7 Dec 2017 08:49:43 -0800 (PST) In-Reply-To: References: <1512658741106-0.post@n6.nabble.com> From: Nikolai Tikhonov Date: Thu, 7 Dec 2017 19:49:43 +0300 X-Gmail-Original-Message-ID: Message-ID: Subject: Re: Affinity - Join query on the collocated data taking 90 seconds To: user@ignite.apache.org Content-Type: multipart/alternative; boundary="94eb2c0851fee9d72f055fc2da6e" archived-at: Thu, 07 Dec 2017 16:49:48 -0000 --94eb2c0851fee9d72f055fc2da6e Content-Type: text/plain; charset="UTF-8" Look at there [1] how to use explain statement. 1. https://apacheignite-sql.readme.io/docs/performance-and-debugging#using-explain-statement On Thu, Dec 7, 2017 at 7:43 PM, Nikolai Tikhonov wrote: > Hi, > > Did you create indexes for PARTY_ID fields? > Any way, can you share explain for the query and also try to rewrite the > query via inner join? > > On Thu, Dec 7, 2017 at 5:59 PM, Naveen wrote: > >> Hi >> >> AM using 2.3 >> Have 2 caches >> Customer - PartyId is the Primary Key >> Account - AccountId is the primary key and also has another column called >> PartyId >> >> While storing the Account data, I am using AffinityKey> PartyId>, >> so that my below join query works since the data is collocated, I could >> get >> the result for the below query without distributedJoins=true, means my >> understanding is data collocated, thats why it is returning the data. But >> it >> is taking 90 secs. >> >> select P.PARTY_ID, A.PARTY_ID, P.ACCOUNT_ID_LIST from "Customer".Customer >> P, >> "Account".Account A where P.PARTY_ID='P101000001' and P.PARTY_ID= >> A.PARTY_ID; >> >> Results of the Query >> >> [tibusr@JMNGD1BAQ10V05 bin]$ ./sqlline.sh --color=true --verbose=true -u >> jdbc:ignite:thin://127.0.0.1 >> issuing: !connect jdbc:ignite:thin://127.0.0.1 '' '' >> org.apache.ignite.IgniteJdbcThinDriver >> Connecting to jdbc:ignite:thin://127.0.0.1 >> Connected to: Apache Ignite (version 2.3.0#20171028-sha1:8add7fd5) >> Driver: Apache Ignite Thin JDBC Driver (version >> 2.3.0#20171028-sha1:8add7fd5) >> Autocommit status: true >> Transaction isolation: TRANSACTION_REPEATABLE_READ >> sqlline version 1.3.0 >> 0: jdbc:ignite:thin://127.0.0.1> select P.PARTY_ID, A.PARTY_ID, >> P.ACCOUNT_ID_LIST from "Customer".Customer P, "Account".Account A where >> P.PARTY_ID='P101000001' and P.PARTY_ID= A.PARTY_ID; >> +--------------------------------+-------------------------- >> ------+--------------------------------+ >> | PARTY_ID | PARTY_ID | >> ACCOUNT_ID_LIST | >> +--------------------------------+-------------------------- >> ------+--------------------------------+ >> | P101000001 | P101000001 | >> A101000001 | >> +--------------------------------+-------------------------- >> ------+--------------------------------+ >> 1 row selected (89.95 seconds) >> 0: jdbc:ignite:thin://127.0.0.1> select P.PARTY_ID, A.PARTY_ID, >> P.ACCOUNT_ID_LIST from "Customer".Customer P, "Account".Account A where >> P.PARTY_ID='P100000001' and P.PARTY_ID= A.PARTY_ID; >> +--------------------------------+-------------------------- >> ------+--------------------------------+ >> | PARTY_ID | PARTY_ID | >> ACCOUNT_ID_LIST | >> +--------------------------------+-------------------------- >> ------+--------------------------------+ >> | P100000001 | P100000001 | >> A100000001 | >> +--------------------------------+-------------------------- >> ------+--------------------------------+ >> 1 row selected (90.984 seconds) >> 0: jdbc:ignite:thin://127.0.0.1> select P.PARTY_ID, A.PARTY_ID, >> P.ACCOUNT_ID_LIST from "Customer".Customer P, "Account".Account A where >> P.PARTY_ID='P100000002' and P.PARTY_ID= A.PARTY_ID; >> +--------------------------------+-------------------------- >> ------+--------------------------------+ >> | PARTY_ID | PARTY_ID | >> ACCOUNT_ID_LIST | >> +--------------------------------+-------------------------- >> ------+--------------------------------+ >> +--------------------------------+-------------------------- >> ------+--------------------------------+ >> No rows selected (90.985 seconds) >> 0: jdbc:ignite:thin://127.0.0.1> select P.PARTY_ID, A.PARTY_ID, >> P.ACCOUNT_ID_LIST from "Customer".Customer P, "Account".Account A where >> P.PARTY_ID='P101000001' and P.PARTY_ID= A.PARTY_ID; >> +--------------------------------+-------------------------- >> ------+--------------------------------+ >> | PARTY_ID | PARTY_ID | >> ACCOUNT_ID_LIST | >> +--------------------------------+-------------------------- >> ------+--------------------------------+ >> | P101000001 | P101000001 | >> A101000001 | >> +--------------------------------+-------------------------- >> ------+--------------------------------+ >> 1 row selected (88.456 seconds) >> 0: jdbc:ignite:thin://127.0.0.1> Closing: >> org.apache.ignite.internal.jdbc.thin.JdbcThinConnection >> [tibusr@JMNGD1BAQ10V05 bin]$ >> [tibusr@JMNGD1BAQ10V05 bin]$ ./sqlline.sh --color=true --verbose=true -u >> jdbc:ignite:thin://127.0.0.1?collacated=true >> issuing: !connect jdbc:ignite:thin://127.0.0.1?collacated=true '' '' >> org.apache.ignite.IgniteJdbcThinDriver >> Connecting to jdbc:ignite:thin://127.0.0.1?collacated=true >> Connected to: Apache Ignite (version 2.3.0#20171028-sha1:8add7fd5) >> Driver: Apache Ignite Thin JDBC Driver (version >> 2.3.0#20171028-sha1:8add7fd5) >> Autocommit status: true >> Transaction isolation: TRANSACTION_REPEATABLE_READ >> sqlline version 1.3.0 >> 0: jdbc:ignite:thin://127.0.0.1> select P.PARTY_ID, A.PARTY_ID, >> P.ACCOUNT_ID_LIST from "Customer".Customer P, "Account".Account A where >> P.PARTY_ID='P101000001' and P.PARTY_ID= A.PARTY_ID; >> +--------------------------------+-------------------------- >> ------+--------------------------------+ >> | PARTY_ID | PARTY_ID | >> ACCOUNT_ID_LIST | >> +--------------------------------+-------------------------- >> ------+--------------------------------+ >> | P101000001 | P101000001 | >> A101000001 | >> +--------------------------------+-------------------------- >> ------+--------------------------------+ >> 1 row selected (94.456 seconds) >> 0: jdbc:ignite:thin://127.0.0.1> >> >> With collocated=true or without literally no difference in response times. >> >> How can improve the response times?? >> >> Have seen the same thread in the community on affinity on the response >> time, but not found any solution. >> >> Thanks >> Naveen >> >> >> >> >> -- >> Sent from: http://apache-ignite-users.70518.x6.nabble.com/ >> > > --94eb2c0851fee9d72f055fc2da6e Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Look at there [1] how to use explain statement.

On Thu, Dec 7, 2017 at = 7:43 PM, Nikolai Tikhonov <ntikhonov@apache.org> wrote:
Hi,

Did you create indexes for PARTY_ID fields?=C2=A0
Any way, can= you share explain for the query and also try to rewrite the query via inne= r join?

On Thu, Dec 7, 2017 at 5:59= PM, Naveen <naveen.bandaru@gmail.com> wrote:
Hi

AM using 2.3
Have 2 caches
Customer - PartyId is the Primary Key
Account - AccountId is the primary key and also has another column called PartyId

While storing the Account data, I am using AffinityKey<AccountId, PartyI= d>,
so that my below join query works since the data is collocated, I could get=
the result for the below query without distributedJoins=3Dtrue, means my understanding is data collocated, thats why it is returning the data. But i= t
is taking 90 secs.

select P.PARTY_ID, A.PARTY_ID, P.ACCOUNT_ID_LIST from "Customer".= Customer P,
"Account".Account=C2=A0 A where P.PARTY_ID=3D'P101000001'= and P.PARTY_ID=3D
A.PARTY_ID;

Results of the Query

[tibusr@JMNGD1BAQ10V05 bin]$ ./sqlline.sh --color=3Dtrue --verbose=3Dtrue -= u
jdbc:ignite:thin://127.0.0.1
issuing: !connect jdbc:ignite:thin://127.0.0.1 '' ''
org.apache.ignite.IgniteJdbcThinDriver
Connecting to jdbc:ignite:thin://127.0.0.1
Connected to: Apache Ignite (version 2.3.0#20171028-sha1:8add7fd5)
Driver: Apache Ignite Thin JDBC Driver (version
2.3.0#20171028-sha1:8add7fd5)
Autocommit status: true
Transaction isolation: TRANSACTION_REPEATABLE_READ
sqlline version 1.3.0
0: jdbc:ignite:thin://127.0.0.1> select P.PARTY_ID, A.PARTY_ID,
P.ACCOUNT_ID_LIST from "Customer".Customer P, "Account"= .Account=C2=A0 A where
P.PARTY_ID=3D'P101000001' and P.PARTY_ID=3D A.PARTY_ID;
+--------------------------------+-------------------------------= -+--------------------------------+
|=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 PARTY_ID=C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 PARTY_ID=C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |
ACCOUNT_ID_LIST=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|
+--------------------------------+-------------------------------= -+--------------------------------+
| P101000001=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0| P101000001=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0|
A101000001=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0|
+--------------------------------+-------------------------------= -+--------------------------------+
1 row selected (89.95 seconds)
0: jdbc:ignite:thin://127.0.0.1> select P.PARTY_ID, A.PARTY_ID,
P.ACCOUNT_ID_LIST from "Customer".Customer P, "Account"= .Account=C2=A0 A where
P.PARTY_ID=3D'P100000001' and P.PARTY_ID=3D A.PARTY_ID;
+--------------------------------+-------------------------------= -+--------------------------------+
|=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 PARTY_ID=C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 PARTY_ID=C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |
ACCOUNT_ID_LIST=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|
+--------------------------------+-------------------------------= -+--------------------------------+
| P100000001=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0| P100000001=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0|
A100000001=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0|
+--------------------------------+-------------------------------= -+--------------------------------+
1 row selected (90.984 seconds)
0: jdbc:ignite:thin://127.0.0.1> select P.PARTY_ID, A.PARTY_ID,
P.ACCOUNT_ID_LIST from "Customer".Customer P, "Account"= .Account=C2=A0 A where
P.PARTY_ID=3D'P100000002' and P.PARTY_ID=3D A.PARTY_ID;
+--------------------------------+-------------------------------= -+--------------------------------+
|=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 PARTY_ID=C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 PARTY_ID=C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |
ACCOUNT_ID_LIST=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|
+--------------------------------+-------------------------------= -+--------------------------------+
+--------------------------------+-------------------------------= -+--------------------------------+
No rows selected (90.985 seconds)
0: jdbc:ignite:thin://127.0.0.1> select P.PARTY_ID, A.PARTY_ID,
P.ACCOUNT_ID_LIST from "Customer".Customer P, "Account"= .Account=C2=A0 A where
P.PARTY_ID=3D'P101000001' and P.PARTY_ID=3D A.PARTY_ID;
+--------------------------------+-------------------------------= -+--------------------------------+
|=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 PARTY_ID=C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 PARTY_ID=C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |
ACCOUNT_ID_LIST=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|
+--------------------------------+-------------------------------= -+--------------------------------+
| P101000001=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0| P101000001=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0|
A101000001=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0|
+--------------------------------+-------------------------------= -+--------------------------------+
1 row selected (88.456 seconds)
0: jdbc:ignite:thin://127.0.0.1> Closing:
org.apache.ignite.internal.jdbc.thin.JdbcThinConnection
[tibusr@JMNGD1BAQ10V05 bin]$
[tibusr@JMNGD1BAQ10V05 bin]$ ./sqlline.sh --color=3Dtrue --verbose=3Dtrue -= u
jdbc:ignite:thin://127.0.0.1?collacated=3Dtrue
issuing: !connect jdbc:ignite:thin://127.0.0.1?collacated=3Dt= rue '' ''
org.apache.ignite.IgniteJdbcThinDriver
Connecting to jdbc:ignite:thin://127.0.0.1?collacated=3Dtrue<= /a>
Connected to: Apache Ignite (version 2.3.0#20171028-sha1:8add7fd5)
Driver: Apache Ignite Thin JDBC Driver (version
2.3.0#20171028-sha1:8add7fd5)
Autocommit status: true
Transaction isolation: TRANSACTION_REPEATABLE_READ
sqlline version 1.3.0
0: jdbc:ignite:thin://127.0.0.1> select P.PARTY_ID, A.PARTY_ID,
P.ACCOUNT_ID_LIST from "Customer".Customer P, "Account"= .Account=C2=A0 A where
P.PARTY_ID=3D'P101000001' and P.PARTY_ID=3D A.PARTY_ID;
+--------------------------------+-------------------------------= -+--------------------------------+
|=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 PARTY_ID=C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 PARTY_ID=C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |
ACCOUNT_ID_LIST=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|
+--------------------------------+-------------------------------= -+--------------------------------+
| P101000001=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0| P101000001=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0|
A101000001=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0|
+--------------------------------+-------------------------------= -+--------------------------------+
1 row selected (94.456 seconds)
0: jdbc:ignite:thin://127.0.0.1>

With collocated=3Dtrue or without literally no difference in response times= .

How can improve the response times??

Have seen the same thread in the community on affinity=C2=A0 on the respons= e
time, but not found any solution.

Thanks
Naveen




--
Sent from: http://apache-ignite-users.70518.x6.= nabble.com/


--94eb2c0851fee9d72f055fc2da6e--