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:49:43 GMT
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 <ntikhonov@apache.org>
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 <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