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]] On Behalf Of
Sergey Soldatov
Sent: Friday, July 21, 2017 1:21 AM
To: [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,
