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 Fri, 21 Jul 2017 06:54:05 GMT
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.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
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