Skip to Content
-1

SAP HANA Performance - Filter pushed down to low in combination with scripted views

Mar 27 at 07:40 AM

98

avatar image
Former Member

We are using HANA calculation views as part of our enterprise services to retrieve data for a single customer (number) using complex calculation views. The lowest level of our calculation views is composed of a range of scripted views (about twenty) that translate the customer number to a policy-id and filter the source data on policy-id. The source tables can have tens of millions of rows. The purpose of this implementation is to minimize the amount of data as early as possible. The customer number is propagated from the highest to the lowest level of our calculation view(s) as a parameter. We expected that this approach could result in low response times of our queries (< 1 sec). We now experience much longer response times: sometimes longer than 1 minute.

We think we have identified the primary causes of the long response times as
• Some filters that we use in the calculation views are pushed downwards in the execution, even below the filtering on customer number/policy-id in the scripted views. That means that the filters are applied on the data of ALL customers before the filtering on customer number is applied. This results in inefficiency and (long) running times of the queries. How can we enforce that filters are always applied above the filtering in the scripted views (i.e. after applying the filtering on customer number)?
• The calculation views are using the same scripted views in different ways. But the result of the scripted views are always the same, because the parameter ‘customer number’ always has the same value. Looking at the execution plan it seems that every USE of the scripted view is executed once. At the bottom of the execution tree we notice significantly more (100+) than twenty leaves (i.e. the number of scripted views we have). How can we enforce that the results of a scripted view are reused (i.e. every scripted view is only executed once)?

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Gijsbert Karens Apr 22 at 09:19 AM
-1

Hi,

We solved the first question by putting the "SEQUENTIAL EXECUTION" command at the beginning of each scripted view. This forced HANA to execute the SQL in right order and filter at the lowest level.

The second question was solved by using Global Temp Tables.

Thank you.




Share
10 |10000 characters needed characters left characters exceeded