HANA calculation view performance tips

[FabioV]

Hi HANA experts,

I'm developing a calculation view and need some suggestions about designing and performance optimization.

In my calculation view I need to UNION several Analytical Views that are all joined (with the same join) to a table containing a column that will be added to the Output of the calculation view.

My question is: it is more performant to implement the Calculation View like in figure 1 (The join is made after the union) or like in fig 2 (the same table and join are replicated for each Analytical view, before the union)?

And Why?

Thank you very much for your answers.

I would really appreciate any insight about how the calculation engine works and how performances can be increased.

Fig 1

Fig 2

[former_member184768 · Accepted solution]

Hi Fabio,

Can you consider the possibility of performing the table join in the Analytic view itself. This way, the join would be performed in the Analytic engine rather than Calc engine, which will provide better performance.

Secondly, I would suggest that you check the visual plan for the query for each of the model (Model 1 and 2 as you suggested and model 3 with the join performed in the Analytic view itself).

If, for some reason, the join could not be performed in the Analytic view, then the first model might provide better performance. But as I suggested, the visual plan will provide you better insight on the performance.

One more point, from the development and maintenance perspective, the second option would be quite difficult to handle and will require higher effort.

Hope this helps.

Regards,

Ravi

[Former Member · Accepted solution]

Hi Ravindra,

I have 2 models whose output is same but differed by design.

In model 1 Projection_1 acts as the input for Projection_2 and Join_12. In Projection_2 half of the columns are used and the data used is half the amount present in Projection_1 .

In model 2 we are using 2 projections for the inputs , Projection_1 serves as the input for Join_12 and input to join_2 is replaced by a

single projection with a filter and selecting only those columns which we require for further proccesing.Remaining design remains the same for both the

models.

Join_12 is left outer join where Projection_1 is the left element and projection_12 is the right element.

When we execute both the models we find that the model 1 is faster than model 2 , even though Projection_2 in model 1 queries the entire set of data and then applies filter to it. And in model 2 the data fetched from db is a filtered set with only required columns.

Looking for your inputs.

Regards

Rohit

[lbreddemann · Accepted solution]

Hi Rohit,

since we don't really have any insight into the amount of data and the actual execution of these views, just two comments:

• the way you create the model does not translate 1:1 to the execution of it. The query optimizers can and will rearrange, add, remove and parallelize parts of the model evaluation. This includes e.g. "double tapping" into the same data source or moving operations down or up the execution order.
  Also, the model is just one part of the story. SAP HANA will dynamically adjust the execution based on the actual query that is run against the model. Without the query, there's really no way to guess what could possibly happen with the model.
  So, looking at the model very often gives a misleading idea of the actual execution.
• As Shreepad already posted: PlanViz is the tool that will provide insight into how a query gets actually executed and what parts took longest.
  From there one can work back to make model adjustments.

Just as with SQL programming, the information model design is declarative. You don't tell the computer what steps to do, but describe the overall computation.

Ideas like "do this first (e.g. apply a filter), then do this (e.g. aggregate)" only apply in terms of data flow logic, not in terms of execution steps.

- Lars

[Former Member · Accepted solution]

Thanks Lars for you inputs. It was helpful in clearing our doubt.

Regards

Rohit

[FabioV]

Thank you to both Shreepad and Ravi.

I didn't know the Visual Plan tool: it is very cool indeed and there's a lot to discover and learn using it.

Fabio

[Former Member]

Hi, Fabio

The Join should be performed on a minimal resultset.

Fig1:

If the Union increases the number of rows then its bad for the join above it. The Join could take more time to process.

Fig2.

Though multiple joins are performed, they all should be processed in parallel.

I think Fig2 should provide a better performance.

Of course Visual Plan will provide a deeper insight.

A deeper analysis can be done on the performance if we can dig inside the Analytic Views as well.

--Shreepad