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 C7F1B200D4E for ; Thu, 7 Dec 2017 15:59:10 +0100 (CET) Received: by cust-asf.ponee.io (Postfix) id C7223160C1E; Thu, 7 Dec 2017 14:59:10 +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 E5B4D160BFE for ; Thu, 7 Dec 2017 15:59:09 +0100 (CET) Received: (qmail 14207 invoked by uid 500); 7 Dec 2017 14:59:09 -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 14197 invoked by uid 99); 7 Dec 2017 14:59:08 -0000 Received: from pnap-us-west-generic-nat.apache.org (HELO spamd1-us-west.apache.org) (209.188.14.142) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 07 Dec 2017 14:59:08 +0000 Received: from localhost (localhost [127.0.0.1]) by spamd1-us-west.apache.org (ASF Mail Server at spamd1-us-west.apache.org) with ESMTP id 30ED1C768C for ; Thu, 7 Dec 2017 14:59:08 +0000 (UTC) X-Virus-Scanned: Debian amavisd-new at spamd1-us-west.apache.org X-Spam-Flag: NO X-Spam-Score: 2.972 X-Spam-Level: ** X-Spam-Status: No, score=2.972 tagged_above=-999 required=6.31 tests=[DKIM_ADSP_CUSTOM_MED=0.001, KAM_ASCII_DIVIDERS=0.8, NML_ADSP_CUSTOM_MED=1.2, SPF_HELO_PASS=-0.001, SPF_SOFTFAIL=0.972] autolearn=disabled Received: from mx1-lw-us.apache.org ([10.40.0.8]) by localhost (spamd1-us-west.apache.org [10.40.0.7]) (amavisd-new, port 10024) with ESMTP id yjeYcR8NFGqF for ; Thu, 7 Dec 2017 14:59:07 +0000 (UTC) Received: from n6.nabble.com (n6.nabble.com [162.255.23.37]) by mx1-lw-us.apache.org (ASF Mail Server at mx1-lw-us.apache.org) with ESMTP id EB8BE5F36E for ; Thu, 7 Dec 2017 14:59:06 +0000 (UTC) Received: from n6.nabble.com (localhost [127.0.0.1]) by n6.nabble.com (Postfix) with ESMTP id 1AC26354436D for ; Thu, 7 Dec 2017 07:59:01 -0700 (MST) Date: Thu, 7 Dec 2017 07:59:01 -0700 (MST) From: Naveen To: user@ignite.apache.org Message-ID: <1512658741106-0.post@n6.nabble.com> Subject: Affinity - Join query on the collocated data taking 90 seconds MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit archived-at: Thu, 07 Dec 2017 14:59:11 -0000 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, 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/