Hi , Yes both have the same java heap . Please do let me know if you are using anything specific while submitting your query or you have any pointers in this regard.
Thanks, Siddharth -----Original Message----- From: Josh Elser [mailto:els...@apache.org] Sent: Friday, July 21, 2017 7:58 PM To: user@phoenix.apache.org Subject: Re: Difference in response time for Join queries with a hint.(ResultSet.next() takes a lot of time ) Are you giving equal amounts of Java heap to both applications? On 7/21/17 5:04 AM, Siddharth Ubale wrote: > Hi, > > Using phoenix 4.10 with hbase0.98. > > Thanks, > > Siddharth > > *From:*Siddharth Ubale [mailto:siddharth.ub...@syncoms.com] > *Sent:* Friday, July 21, 2017 12:24 PM > *To:* user@phoenix.apache.org > *Subject:* RE: Difference in response time for Join queries with a > hint.(ResultSet.next() takes a lot of time ) > > Hi Sergey, > > Yes Sergey. We are also on the same page with you hence we were > wondering about this behavior. > > Please find my queries below : > > *Query 1: * > > select terms.terms292fec765f1c486f85a509d88096f098tenantid > > ,terms.terms292fec765f1c486f85a509d88096f098objectid > > ,customer.olap4customer292fec765f1c486f85a509d88096f098cid > > ,customer.olap4customer292fec765f1c486f85a509d88096f098name > > ,customer.olap4address292FEC765F1C486F85A509D88096F098street > > ,customer.olap4address292FEC765F1C486F85A509D88096F098pin > > ,customer.olap4vendor292FEC765F1C486F85A509D88096F098vid > > ,customer.olap4vendor292FEC765F1C486F85A509D88096F098vname > > ,customer.olapcompany1292FEC765F1C486F85A509D88096F098cname > > ,customer.olapcompany1292FEC765F1C486F85A509D88096F098location > > from datawarehouse_chk as terms > > inner join > > ( > > /*Customer - relationship*/ > > SELECT /* RANGE_SCAN */ > > customer.olap4customer292fec765f1c486f85a509d88096f098cid > > ,customer.olap4customer292fec765f1c486f85a509d88096f098name > > ,relationship.olap4address292FEC765F1C486F85A509D88096F098street > > ,relationship.olap4address292FEC765F1C486F85A509D88096F098pin > > ,relationship.olap4vendor292FEC765F1C486F85A509D88096F098vid > > ,relationship.olap4vendor292FEC765F1C486F85A509D88096F098vname > > ,relationship.olapcompany1292FEC765F1C486F85A509D88096F098cname > > ,relationship.olapcompany1292FEC765F1C486F85A509D88096F098location > > FROM datawarehouse_chk AS customer > > inner JOIN > > ( > > /*Address - relationship*/ > > SELECT > relationship.parentobjectdatarowkey > > > ,Address.olap4address292FEC765F1C486F85A509D88096F098street > > > ,Address.olap4address292FEC765F1C486F85A509D88096F098pin > > > ,Address.olap4vendor292FEC765F1C486F85A509D88096F098vid > > > ,Address.olap4vendor292FEC765F1C486F85A509D88096F098vname > > > ,Address.olapcompany1292FEC765F1C486F85A509D88096F098cname > > ,Address. > olapcompany1292FEC765F1C486F85A509D88096F098location > > FROM relationship_data AS > relationship > > inner JOIN > > ( > > /*vendor - relationship*/ > > SELECT > relationship.parentobjectdatarowkey, > > > olap4vendor292fec765f1c486f85a509d88096f098objectid, > > > olap4vendor292fec765f1c486f85a509d88096f098vid, > > > olap4vendor292fec765f1c486f85a509d88096f098vname, > > > relationship.olap4address292FEC765F1C486F85A509D88096F098street, > > > > relationship.olap4address292fec765f1c486f85a509d88096f098pin, > > > relationship.olapcompany1292FEC765F1C486F85A509D88096F098cname, > > > relationship.olapcompany1292FEC765F1C486F85A509D88096F098location > > FROM datawarehouse_chk AS vendor > > inner JOIN > > ( /*Address - Relationship*/ > > SELECT > address.olap4address292FEC765F1C486F85A509D88096F098street > > > ,address.olap4address292fec765f1c486f85a509d88096f098pin, > > > address.olapcompany1292FEC765F1C486F85A509D88096F098cname, > > > address. olapcompany1292FEC765F1C486F85A509D88096F098location, > > > childobjectdatarowkey,parentobjectdatarowkey > > FROM relationship_data > AS relationship > > inner JOIN > > ( /*Address*/ > > SELECT > address.rk, > > > address.olap4address292fec765f1c486f85a509d88096f098objectid, > > > > address.olap4address292FEC765F1C486F85A509D88096F098street, > > > address.olap4address292fec765f1c486f85a509d88096f098pin, > > > country.olapcompany1292FEC765F1C486F85A509D88096F098cname, > > > country.olapcompany1292FEC765F1C486F85A509D88096F098location > > FROM > datawarehouse_chk AS address > > > > > INNER JOIN datawarehouse_chk AS country ON > address.olap4address292FEC765F1C486F85A509D88096F098aid = > country.olapcompany1292FEC765F1C486F85A509D88096F098versionnum > > > WHERE address.olap4address292FEC765F1C486F85A509D88096F098street = 'MG > road190' AND > > address.rk > LIKE '292FEC76-5F1C-486F-85A5-09D88096F098olap4address%' AND > > > country.rk LIKE > '292FEC76-5F1C-486F-85A5-09D88096F098olapcompany%' > > > /*Address*/ > > ) AS address ON > address.rk = relationship.parentobjectdatarowkey > > WHERE > relationship.tenant_parentobjectid = 'a5805b8b-103a-4786-ade9-bedfa0158b59' > > AND > relationship.tenant_childobjectid = '87be6d98-0f9b-4f44-bcd3-87544c6dc358' > > > ) AS relationship ON relationship.childobjectdatarowkey = > vendor.rk > > where > vendor.olap4vendor292FEC765F1C486F85A509D88096F098vname='BDM 21' and > > rk like > '292FEC76-5F1C-486F-85A5-09D88096F098olap4vendor%' > > /*vendor - > relationship*/ > > )AS Address ON > Address.parentobjectdatarowkey = relationship.childobjectdatarowkey > > where > relationship.tenant_ParentOBJECTID='81aa279d-1b0a-409a-bcee-bb09da603d3e' > > AND > relationship.tenant_childobjectid = 'a5805b8b-103a-4786-ade9-bedfa0158b59' > > /*Address - relationship*/ > > ) AS relationship ON relationship.parentobjectdatarowkey = > customer.rk > > where customer.olap4customer292FEC765F1C486F85A509D88096F098Name = > 'Prasad 0' and > > rk like '292FEC76-5F1C-486F-85A5-09D88096F098olap4customer%' > > /*Customer - relationship*/ > > ) > > as customer > > on customer.olap4customer292FEC765F1C486F85A509D88096F098cid = > terms.terms292FEC765F1C486F85A509D88096F098tenantid > > where terms.terms292FEC765F1C486F85A509D88096F098tenantid = 'c83' and > > terms.rk like '292FEC76-5F1C-486F-85A5-09D88096F098terms%' > > Query 2: > > select /*+ USE_SORT_MERGE_JOIN */ > > olap4customer.olap4customer292FEC765F1C486F85A509D88096F098cid, > > olap4customer.olap4customer292FEC765F1C486F85A509D88096F098Name as > Name, > > olap4address.olap4address292FEC765F1C486F85A509D88096F098aid, > > olap4address.olap4address292FEC765F1C486F85A509D88096F098street, > > olap4vendor.olap4vendor292FEC765F1C486F85A509D88096F098vname , > > terms.terms292fec765f1c486f85a509d88096f098tenantid > > from (select > rk,olap4customer292FEC765F1C486F85A509D88096F098cid,olap4customer292FE > C765F1C486F85A509D88096F098Name > from datawarehouse_chk where rk like > '292FEC76-5F1C-486F-85A5-09D88096F098olap4customer%' ) as > olap4customer > > inner join > > ( select parentobjectdatarowkey,childobjectdatarowkey from > relationship_data > > where tenant_parentobjectid > ='81aa279d-1b0a-409a-bcee-bb09da603d3e' and tenant_childobjectid > ='a5805b8b-103a-4786-ade9-bedfa0158b59') > > as r1 on r1.parentobjectdatarowkey = olap4customer.rk > > inner join ( select > olap4address292FEC765F1C486F85A509D88096F098aid,olap4address292FEC765F > 1C486F85A509D88096F098street,rk from datawarehouse_chk where rk like > '292FEC76-5F1C-486F-85A5-09D88096F098olap4address%') as olap4address > on r1.childobjectdatarowkey=olap4address.rk > > inner join > > ( select parentobjectdatarowkey,childobjectdatarowkey from > relationship_data > > where tenant_parentobjectid > ='a5805b8b-103a-4786-ade9-bedfa0158b59' and tenant_childobjectid > ='87be6d98-0f9b-4f44-bcd3-87544c6dc358') > > as r2 on r2.parentobjectdatarowkey = olap4address.rk > > inner join (select > rk,olap4vendor292FEC765F1C486F85A509D88096F098vname > from datawarehouse_chk where rk like > '292FEC76-5F1C-486F-85A5-09D88096F098olap4vendor%' ) as olap4vendor on > r2.childobjectdatarowkey =olap4vendor.rk > > inner join (select > rk,olapcompany1292FEC765F1C486F85A509D88096F098versionnum from > datawarehouse_chk where rk like > '292FEC76-5F1C-486F-85A5-09D88096F098olapcompany%') as company on > olap4address.olap4address292FEC765F1C486F85A509D88096F098aid = > company.olapcompany1292FEC765F1C486F85A509D88096F098versionnum > > inner join (select > rk,terms292FEC765F1C486F85A509D88096F098tenantid,terms292FEC765F1C486F > 85A509D88096F098objectid from datawarehouse_chk where rk like > '292FEC76-5F1C-486F-85A5-09D88096F098terms%') as terms on > olap4customer.olap4customer292FEC765F1C486F85A509D88096F098cid = > terms.terms292FEC765F1C486F85A509D88096F098tenantid > > where olap4customer.olap4customer292FEC765F1C486F85A509D88096F098Name > ='Prasad 0' > > and olap4vendor.olap4vendor292FEC765F1C486F85A509D88096F098vname ='BDM 21' > > and > olap4address.olap4address292FEC765F1C486F85A509D88096F098street='MG > road190' > > and terms.terms292FEC765F1C486F85A509D88096F098tenantid = 'c83' > > and company.olapcompany1292FEC765F1C486F85A509D88096F098versionnum is > not null > > You can use the above 2 queries to reproduce the issue. > > Thanks, > > Siddharth Ubale > > *From:*sergey.solda...@gmail.com <mailto:sergey.solda...@gmail.com> > [mailto:sergey.solda...@gmail.com] *On Behalf Of *Sergey Soldatov > *Sent:* Friday, July 21, 2017 1:21 AM > *To:* user@phoenix.apache.org <mailto:user@phoenix.apache.org> > *Subject:* Re: Difference in response time for Join queries with a > hint.(ResultSet.next() takes a lot of time ) > > Hi Siddharth, > > That's sounds strange because sqlline tool is just an another db > client and it uses the same JDBC API. By any chance can you provide > the DDLs and queries, so we will be able to reproduce the problem? > > Thanks, > > Sergey > > On Wed, Jul 19, 2017 at 11:16 PM, Siddharth Ubale > <siddharth.ub...@syncoms.com <mailto:siddharth.ub...@syncoms.com>> wrote: > > Hi , > > I am executing 2 join queries which give the same response. > > Query 1: Nested join over 5 tables where . The query uses rowkey > “like” statement to narrow down to the filtered columns. This query > in Phoenix sqlline terminal takes 800millisecs to execute. > > Query 2: Is a inner join over the same 5 tables with secondary > indexes used, it uses the HINT /*+ USE_SORT_MERGE_JOIN */. This > query takes approx. 700millisecs to execute on sqlline terminal. > > When we use JDBC to execute the same above 2 queries . > > >Query 1 takes approx. 900 ms to 1000ms to execute and provide results. > > >However query2 takes approx. 3 secs to produce the result. And > time taken in the query on further analysis shows that > resultset.next() takes bulk of this time. Why is is that the > terminal takes such less time and the JDBC API is taking so much > time to execute the query? > > Please do let me know if anyone faced this issue and is there any > way I can ensure that JDBC call with inner join query is executed > faster. > > Thanks, > > Siddharth Ubale, > > // >
