You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@phoenix.apache.org by Siddharth Ubale <si...@syncoms.com> on 2017/07/20 06:16:02 UTC
Difference in response time for Join queries with a
hint.(ResultSet.next() takes a lot of time )
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 )
Posted by Josh Elser <el...@apache.org>.
The "Phoenix console client" is using the JDBC driver. This is no
distinction between how these two approaches would run code, only the
environment in which they're invoked.
Have you ensured that your Java application includes hbase-site.xml on
its classpath? I am trying to think of all of the things that sqlline.py
sets up automatically (you can inspect this script to do the same).
For a SORT_MERGE_JOIN, yes, the client must merge the sorted streams of
data being read from HBase. This is essentially the definition of a
sort-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:siddharth.ubale@syncoms.com]
> Sent: Tuesday, July 25, 2017 10:27 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 ,
>
> 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 <ma...@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 <ma...@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 <ma...@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,
>>
>> //
>>
RE: Difference in response time for Join queries with a
hint.(ResultSet.next() takes a lot of time )
Posted by Siddharth Ubale <si...@syncoms.com>.
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:siddharth.ubale@syncoms.com]
Sent: Tuesday, July 25, 2017 10:27 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 ,
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 <ma...@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 <ma...@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 <ma...@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,
>
> //
>
RE: Difference in response time for Join queries with a
hint.(ResultSet.next() takes a lot of time )
Posted by Siddharth Ubale <si...@syncoms.com>.
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 <ma...@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 <ma...@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 <ma...@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,
>
> //
>
Re: Difference in response time for Join queries with a
hint.(ResultSet.next() takes a lot of time )
Posted by Josh Elser <el...@apache.org>.
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,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 <ma...@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 <ma...@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 <ma...@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,
>
> //
>
RE: Difference in response time for Join queries with a
hint.(ResultSet.next() takes a lot of time )
Posted by Siddharth Ubale <si...@syncoms.com>.
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,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<ma...@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<ma...@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 <si...@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,
RE: Difference in response time for Join queries with a
hint.(ResultSet.next() takes a lot of time )
Posted by Siddharth Ubale <si...@syncoms.com>.
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 <si...@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,
Re: Difference in response time for Join queries with a
hint.(ResultSet.next() takes a lot of time )
Posted by Sergey Soldatov <se...@gmail.com>.
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> 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,
>
>
>
>
>