phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Josh Elser <els...@apache.org>
Subject Re: Difference in response time for Join queries with a hint.(ResultSet.next() takes a lot of time )
Date Wed, 02 Aug 2017 16:16:25 GMT
The "Phoenix console client" is using the JDBC driver. This is no 
distinction between how these two approaches would run code, only the 
environment in which they're invoked.

Have you ensured that your Java application includes hbase-site.xml on 
its classpath? I am trying to think of all of the things that sqlline.py 
sets up automatically (you can inspect this script to do the same).

For a SORT_MERGE_JOIN, yes, the client must merge the sorted streams of 
data being read from HBase. This is essentially the definition of a 
sort-merge join as opposed to a hash join.

On 8/1/17 4:47 AM, Siddharth Ubale wrote:
> Hi,
> 
> This is something that’s not as expected. Same queries taking different times when
we use the Phoenix console client and JDBC java client . Is this as expected?
> Same behavior is seen with phoenix versions 4.10 & 4.9 . Does phoenix do any client
side operations when we use USE_SORT_MERGE_JOIN hint ?
> Kindly pls let me know.
> 
> Thanks,
> Siddharth Ubale
> 
> -----Original Message-----
> From: Siddharth Ubale [mailto:siddharth.ubale@syncoms.com]
> Sent: Tuesday, July 25, 2017 10:27 AM
> 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 ,
> 
> 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:elserj@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.ubale@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.soldatov@gmail.com <mailto:sergey.soldatov@gmail.com>
>> [mailto:sergey.soldatov@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.ubale@syncoms.com <mailto:siddharth.ubale@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,
>>
>>      //
>>

Mime
View raw message