Multi Join Technique

[sreevatsa_adiraju]

Dear Friends,

This Blog post is related to Multi Joins technique in HANA view. In Ideal case, if we have more joins in calculation view will be more expensive.

Today we can learn how we can improve performance using Multiple joins in real time scenarios

Requirement:

In a Calculation view we have requirement to join very huge table, since already view also very big this join will be too expensive and causes Performance problems

SCENARIO A:

We have Sales table as source and having multiple joins already available in existing view and we have requirements to get WBS COST from Project table. For Example, Existing data set has 80 Million Data and Project Data set has 40 Million data

In general, will create joins like below

Above join will take more time than expected, since it will execute lookup for each 80 Million number of Records, even though we have only 5 Million number of unique numbers of WBS from left side

What Can be done?

This Performance problem can be resolved with simple Technique, create Aggregation node with unique WBS elements after sales data and join with required right table.

Once you get desired output data and join back to existing view so that we are minimizing data while joining to huge tables.

 

SCENARIO B,

We have GLPCA Data set with other joins and finally join with MKPF using Material document number.

In this Scenario if unique material document count will be same as source data set count, making unique records will not be helpful

What Can be done?

Partition Source data in to Multiple data set, do join Multiple time instead one time and use Union and get final data Set. This will use parallel processing and performance will be improved

To Summarize, Performance improvement can be done using Multiple join instead single join. Depending data and business requirement we can use any one of above technique

Happy Reading  ?