on 03-20-2023 8:58 PM
Hi all ,
I am new to CDS and table functions. I had a requirement where i had to do some calculations in table function and return the same in CDS view. PFB details :
CDS View ZXXX calling the Table Function
Table Function :
AMDP Method :
Now my question is when the CDS view is executed will the table function first execute and execute calculation for all objects before the inner join filter is applied or inner join filter will be applied first and then table function execution will take place ? Just wanted to understand the performance impact in case of using it in this way. In this particular case i cannot pass the input as a parameter else would have passed it and filtered the result already.
Hi Tejas,
Both artifacts you expose (CDS and function) will generate database artifacts (SQL view and Function), that said the execution flow is from data source to consumption point.
In terms of execution order, normally HANA will read the SQL fired and create a runtime version that can create a new "model" that integrate all the execution like one big SQL. Also, some parts which is independant can execute in parallel, so it's not procedural.
You cds execution will initiate the join of IFLO / T499s at same time VSTAT / T352R... the result from IFLO / T499s will wait finish further joins result / TJ02T / TJ30T / agregation... to join both results...
Regards, Fernando Da Rós
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi tejas.deshpande24 ,
REVNR will not be passed as parameter, as it is not a parameter. What can be passed as parameter is what is constant from your query and not from the data which came from the result of your query.
Example that works really good:
SELECT .. FROM example_View_with_many_joins WHERE YEAR = '2023'. --> In this scenario HANA has the opportunity to take this 2023 through views from where the column table level, reducing the amount of data read. This is what we call pushing down parameters
Example like yours:
SELECT ... FROM example_View_with_many_joins as a inner join dummy_code_value_table as b on a.YEAR = b.VALUE and b.CODE = 'YEAR_TO_RUN_MY_QUERY' --> in this scenario there's no constant, there's no value in hands of the optimizer. HANA will execute both views, and from first view will bring data from all YEARS, as only on your selection it can be "filtered".
Regards, Fernando Da Rós
User | Count |
---|---|
85 | |
11 | |
8 | |
7 | |
7 | |
6 | |
6 | |
6 | |
6 | |
5 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.