phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Siddharth Ubale <siddharth.ub...@syncoms.com>
Subject RE: Difference in response time for Join queries with a hint.(ResultSet.next() takes a lot of time )
Date Tue, 25 Jul 2017 04:56:47 GMT
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