Re: Difference in response time for Join queries with a hint.(ResultSet.next() takes a lot of time )

Josh Elser Wed, 02 Aug 2017 09:17:24 -0700

The "Phoenix console client" is using the JDBC driver. This is nodistinction between how these two approaches would run code, only theenvironment in which they're invoked.

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

For a SORT_MERGE_JOIN, yes, the client must merge the sorted streams ofdata being read from HBase. This is essentially the definition of asort-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:[email protected]]
Sent: Tuesday, July 25, 2017 10:27 AM
To: [email protected]
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:[email protected]]
Sent: Friday, July 21, 2017 7:58 PM
To: [email protected]
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:[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,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:*[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,

     //

Re: Difference in response time for Join queries with a hint.(ResultSet.next() takes a lot of time )

Reply via email to