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 <[email protected]> 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 <[email protected]> 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/ >> > >
