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/ >
