ignite-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Nikolai Tikhonov <ntikho...@apache.org>
Subject Re: Affinity - Join query on the collocated data taking 90 seconds
Date Thu, 07 Dec 2017 16:43:30 GMT
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 <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, 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/
>

Mime
View raw message