Order of Execution in SAP CDS Table Function

[tejas_deshpande24]

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.

[fedaros]

GROUP BY and DISTINCT are performing same action. Anyhow, should be better try remove both to avoid missing filters.

[Sandra_Rossi]

I guess execution plan and SQL trace should help understanding the database choice.

[fedaros · Accepted solution]

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

[tejas_deshpande24 · Accepted solution]

Got it, this really helpted and gave me a better understanding of things. But will the parameter revnr be passed to VSTAT/T352R for filtering or will the table function execute completely and then the filter of revnr will be applied ?

[fedaros · Accepted solution]

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

[tejas_deshpande24 · Accepted solution]

This was really helpful and answers my question too. Basically the inner table function will execute first and then the CDS view filter of revnr will be applied to it.