SAP HANA Caculation View - Performance Issue

[mtayibi]

Hello together,

i have to optimize our calculcation views which has been created by a external company in the past.

Unfortunately these calculation views are having huge performance issue on our SAP HANA System so also operational business is affected when calculations views are running.

Here is an example (extract):

As you can see the source table like ACDOCA are used directly. At the moment the ACODCA has 13.3 million of data sets (filters are applied).

Also we have a huge logic in LV_3:

Logic look like this:

As you can imagine with 13 mio records and such a logic there is a huge calculation process.

So my question is what are the best ways to optimize the performance?

Thanks in advance!

Kr

Benaissa

In the attachment you can find the complete CV: co-incomestatement.jpg

[lbreddemann]

The view definition and the data volume are just two factors of the bad performance you experience.

Another critical factor is the query that uses the view and the data.

The very same view may be working satisfactory with one query and may be too slow with another.

So, the bad performance absolutely needs to be considered together with the specific query (including the filter parameters).

The next step is to verify whether the calculated columns are actually the cause for the bad performance. It may easily be something completely different than hat. To check on that, PlanViz is the tool of choice here.

Finally, by looking at the screenshot of the view it seems that the join cardinalities have not been set for the outer joins. This is typically an error of omission that can have a big impact to the query runtime. With the join cardinalities declared, the query optimiser may decide to leave out joins if this does not change the result set.