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)?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.