Skip to Content
0

HANA model - Filter on the DB table before Join node

Nov 10, 2016 at 09:52 PM

253

avatar image

Hi, I am trying to improve the performance of Calculation view execution in HANA. Actually there are 2 tables, Material master data and Invoice transaction data. The query will have a variable on material, it is always single value entry and mandatory.,

So user input Parameter would take a specific material from the master data and has to join the transaction table to get the invoice amount. But the material number field does not exist in the transaction data table. There is an attribute called 'reference' in material master data table which will be used to join the transaction data.So I will have to project entire data from transaction table for joining, though I just need one record from the transactions, I am wondering how could I make a dynamic filter on the transactions to get only that particular record before joining node?

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

3 Answers

Lars Breddemann
Nov 12, 2016 at 01:36 AM
1

Whenever you like to improve the performance of a query, it's highly advisable to start off by measuring where the execution time is actually spent. PlanViz is the right tool for that.

Once you understand where the time is spent, we can look into whether there are modelling options to improve on that.

Right now, it seems to want to work based off your assumptions about how the query gets executed - in the majority of the cases that will lead to 'fixing' the wrong things.

Share
10 |10000 characters needed characters left characters exceeded
Benedict Venmani Felix Nov 16, 2016 at 05:26 AM
0

Hello Sreekanth,

I had a similar issue a few weeks ago. I had to filter table 'A' and inner join it to another larger table 'B'. But, as Lars said, when I looked at the execution plan, I saw that HANA was intelligent enough to execute the join and then apply the filter on the resultant data. Since inner join and filter are commutative.

Actually while looking at the plan, the logical plan showed the entire table B getting projected. But a closer look at the physical plan showed the join getting executed first.

-Benedict

Share
10 |10000 characters needed characters left characters exceeded
Sreekanth Surampally Nov 16, 2016 at 08:34 PM
0

That is good to know, I haven't noticed it, let me run the plan execution and see what happens. Thank you both.

Share
10 |10000 characters needed characters left characters exceeded