[IQUG] [EXTERNAL] IQUG Digest, Vol 81, Issue 17

John KIRKWOOD john.kirkwood at uk.bnpparibas.com
Tue Sep 24 01:18:02 MST 2019


This is interesting but I am puzzled by the statement

"the views do not cost anything in IQ"

Why not? You have got to access the column either way. If the column is indexed so that it has cardinality stats these are available to both types of view and the performance improvement will be low making push down of the group by not that much better ??

John



Classification: Internal
-----Original Message-----
From: iqug-bounces at iqug.org <iqug-bounces at iqug.org> On Behalf Of iqug-request at iqug.org
Sent: 22 September 2019 20:00
To: iqug at iqug.org
Subject: [EXTERNAL] IQUG Digest, Vol 81, Issue 17

Send IQUG mailing list submissions to
	iqug at iqug.org

To subscribe or unsubscribe via the World Wide Web, visit
	http://iqug.org/mailman/listinfo/iqug
or, via email, send a message with subject or body 'help' to
	iqug-request at iqug.org

You can reach the person managing the list at
	iqug-owner at iqug.org

When replying, please edit your Subject line so it is more specific than "Re: Contents of IQUG digest..."


Today's Topics:

   1. Re: pushdown of group by (John Anthony)


----------------------------------------------------------------------

Message: 1
Date: Sun, 22 Sep 2019 12:18:27 -0500
From: John Anthony <chirayithaj at gmail.com>
To: "Mumy, Mark" <mark.mumy at sap.com>
Cc: "iqug at iqug.org" <iqug at iqug.org>, "Alhandra, Hugo,	Vodafone Group
	\(External\)" <hugo.alhandra at vodafone.com>
Subject: Re: [IQUG] pushdown of group by
Message-ID:
	<CANiW5cHJ3Cyt2cMeo3zOhmFbuLT3TFFyiOkkp55VNFUgDCeH0Q at mail.gmail.com>
Content-Type: text/plain; charset="utf-8"

Mark hit the nail on the head! As always !!

As DBAs , we are kind of spoiled by IQ performance, as compared to the other options available. But as data volumes increase, even small optimizer miscalculations lead to hung queries. We are hitting clear limits to this 'DBA approach'.

The HANA approach for IQ, given HANA has features IQ will never get,starts with :-
- Force users to start queries with summarized data, not exposing key values, and creating date part columns that are deterministic to the summarized column.
- expose details and key values in multiple views, grouped by function, to force usage via summarized views
- provide join options from the summarized views, to prevent stand alone usage of detailed data views

The idea is to provide high speed pathways based on data/business semantics. obviously, there will always be certain situation that require special handling, and will need one off treatment.

HTH,

-JA

On Fri, Sep 20, 2019 at 3:26 PM Mumy, Mark <mark.mumy at sap.com> wrote:

> What John is saying is that you provide a series of views to do 
> different types of queries:
>
>
>
> Create view v1_count as
>
> Select c1, count(*) cnt_col from t1 group by c1
>
> Union all
>
> Select c1, count(*) cnt_col from t2 group by c1
>
> Union all
>
> Select c1, count(*)  cnt_col from t3 group by c1;
>
>
>
> Then a ?select c1, cnt_col from v1_cnt?
>
>
>
> Or this:
>
> Create view v1_sumc2 as
>
> Select c1, sum(c2) sum_c2 from t1 group by c1
>
> Union all
>
> Select c1, sum(c2) sum_c2 cnt_col from t2 group by c1
>
> Union all
>
> Select c1, sum(c2) sum_c2 from t3 group by c1;
>
>
>
> Then a ?select c1, sum( sum_c2 ) from v1_sumc2 group by c1?
>
>
>
> The views don?t cost anything in IQ.  They only cost in development 
> time and app integration time.  We take this approach all the time in 
> HANA as it gives the developer the chance to model their job with 
> performance in mind from the very beginning.
>
>
>
> The model that we?ve historically used with IQ is the easy approach.  
> Use
> 1 view that has to handle everything and don?t customize it at all.  
> The downside is that a generic approach gives you generic performance, 
> not the best performance.
>
>
>
> Mark
>
>
>
> *Mark Mumy*
>
> *SAP Platform and Technologies Global Center of Excellence*
>
> M +1 347-820-2136 | E mark.mumy at sap.com
>
> My Blogs: https://blogs.sap.com/author/markmumy/
>
>
>
> https://sap.na.pgiconnect.com/I825063
>
> Conference tel: 18663127353,,8035340905#
>
>
>
> *From: *"iqug-bounces at iqug.org" <iqug-bounces at iqug.org> on behalf of 
> "Alhandra, Hugo, Vodafone Group (External)" 
> <hugo.alhandra at vodafone.com>
> *Date: *Friday, September 20, 2019 at 9:25 AM
> *To: *John Anthony <chirayithaj at gmail.com>
> *Cc: *"iqug at iqug.org" <iqug at iqug.org>
> *Subject: *Re: [IQUG] pushdown of group by
>
>
>
> Hi John, thanks a lot for your answer,
>
> I understand it is not an easy job for the optimizer, that?s why we 
> would like to give it a hint.
>
>
>
> Can you elaborate more on this:
>
> ?provide a bunch of end-user exposed views on top of the union all 
> views, that ensure that there will be push down?
>
> How can we ensure there will be a push down?
>
>
>
> Regards,
>
> Hugo
>
>
>
>
>
> *From:* John Anthony <chirayithaj at gmail.com>
> *Sent:* 20 de setembro de 2019 15:17
> *To:* Alhandra, Hugo, Vodafone Group (External) < 
> hugo.alhandra at vodafone.com>
> *Cc:* iqug at iqug.org
> *Subject:* Re: [IQUG] pushdown of group by
>
>
>
> having calculated columns - especially the grouping ones - tends to 
> produce more push down.Simple example with be to have columns like day 
> / month / year with functions on on a date column. This will produce 
> its own issues, occasionally.
>
>
>
> One has to say that the qp has improved a lot with pushing these down 
> on a 'union all' views. But there will always be the one odd query 
> that does not produce the push down, and if the data set is huge, 
> typically it is with these kind of views, the query will not return!
>
>
>
> The additional idea is to not provide direct access to these views, 
> but take the HANA approach, and provide a bunch of end-user exposed 
> views on top of the union all views, that ensure that there will be 
> push down. As i have said before, the IQ community is taking too 
> minimalistic approach to modelling. We not to model in protections to QP issues.
>
>
>
> -JA
>
>
>
>
>
>
>
> On Thu, Sep 19, 2019 at 9:41 AM Alhandra, Hugo, Vodafone Group 
> (External) < hugo.alhandra at vodafone.com> wrote:
>
> Hi,
>
> Do you know how can we force the pushdown of a group by?
>
>
>
> Given this view:
>
>
>
> Create view V1
>
> As
>
> Select c1, c2, c3 from t1
>
> Union all
>
> Select c1, c2, c3 from t2
>
> Union all
>
> Select c1, c2, c3 from t3;
>
>
>
> And this query:
>
> -------------------------
>
> Select c1, count(*) from V1 group by c1
>
>
>
>
>
> Force the QP to perform something like this
>
>
>
> 1) calculate
>
> Select c1,count(*) from t1 group by c1
>
>
>
> 2) calculate
>
> Select c1,count(*) from t2 group by c1
>
>
>
> 3) calculate
>
> Select c1,count(*) from t3 group by c1
>
>
>
> 4) calculate
>
> Select c1, count(*) from (result 1 union all result 2 union all result 
> 3) group by c1
>
>
>
> Regards,
>
> ?
> *Hugo Alhandra*
> Database Administrator
> hugo.alhandra at vodafone.com <hugo.alhandra at corp.vodafone.pt>
>
>
>
> *gfi.pt*
> <https://eur03.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.
> gfi.pt%2F&data=02%7C01%7Chugo.alhandra%40vodafone.com%7C4b870a576b384c
> 52bb4b08d73dd53fb9%7C68283f3b84874c86adb3a5228f18b893%7C0%7C0%7C637045
> 858410621628&sdata=MlzsmsRsddMO6ii3TsLJqA9kTh9sWiMc0TWVwxRuIp0%3D&rese
> rved=0>
> I  *gfi.pt/jobs*
> <https://eur03.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.
> gfi.pt%2Fjobs&data=02%7C01%7Chugo.alhandra%40vodafone.com%7C4b870a576b
> 384c52bb4b08d73dd53fb9%7C68283f3b84874c86adb3a5228f18b893%7C0%7C0%7C63
> 7045858410621628&sdata=jU8sjXlj2fcavGMf3thZ6naf8YDqfIZeabNAXP3ZO7I%3D&
> reserved=0>
> I  *Gfi News*
> <https://eur03.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.
> gfi.pt%2Fgfi-news&data=02%7C01%7Chugo.alhandra%40vodafone.com%7C4b870a
> 576b384c52bb4b08d73dd53fb9%7C68283f3b84874c86adb3a5228f18b893%7C0%7C0%
> 7C637045858410631621&sdata=%2BzPdvsmHg%2BXAjf8Gveq4P18iYDeqNRuk%2FE4sl
> OMNXBk%3D&reserved=0>
>
>
>
>
>
>
>
>
>
> _______________________________________________
> IQUG mailing list
> IQUG at iqug.org
> http://iqug.org/mailman/listinfo/iqug
> <https://eur03.safelinks.protection.outlook.com/?url=http%3A%2F%2Fiqug
> .org%2Fmailman%2Flistinfo%2Fiqug&data=02%7C01%7Chugo.alhandra%40vodafo
> ne.com%7C4b870a576b384c52bb4b08d73dd53fb9%7C68283f3b84874c86adb3a5228f
> 18b893%7C0%7C0%7C637045858410631621&sdata=GmiVHVkHuImQx887%2BLdTzwtKok
> 1w1c%2B9M8Va8XRuT%2BQ%3D&reserved=0>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://iqug.org/pipermail/iqug/attachments/20190922/d9d09e2e/attachment-0001.html>

------------------------------

_______________________________________________
IQUG mailing list
IQUG at iqug.org
http://iqug.org/mailman/listinfo/iqug

End of IQUG Digest, Vol 81, Issue 17
************************************

___________________________________________________________
This e-mail may contain confidential and/or privileged information. If you are not the intended recipient (or have received this e-mail in error) please notify the sender immediately and delete this e-mail. Any unauthorised copying, disclosure or distribution of the material in this e-mail is prohibited.

Please refer to http://www.bnpparibas.co.uk/en/email-disclaimer/ for additional disclosures.



More information about the IQUG mailing list