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,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:*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,
//
