Hi, Using phoenix 4.10 with hbase0.98.
Thanks, Siddharth From: Siddharth Ubale [mailto:[email protected]] Sent: Friday, July 21, 2017 12:24 PM To: [email protected] 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,olap4customer292FEC765F1C486F85A509D88096F098Name 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,olap4address292FEC765F1C486F85A509D88096F098street,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,terms292FEC765F1C486F85A509D88096F098objectid 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: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Sergey Soldatov Sent: Friday, July 21, 2017 1:21 AM To: [email protected]<mailto:[email protected]> 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 <[email protected]<mailto:[email protected]>> 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,
