ignite-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Naveen <naveen.band...@gmail.com>
Subject Affinity - Join query on the collocated data taking 90 seconds
Date Thu, 07 Dec 2017 14:59:01 GMT
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