Why are CE functions faster than SQL?

[Former Member]

Hi,

I know that calculation views built using CE functions give much faster results as compared to calc views using SQL.

I am also aware of the fact that pruning helps in improving the performance.

But if we have a table and we expose all the columns in a script calc view using 1.CE functions and 2.SQL functions

we can see that CE function based calc view is much faster if we do a select * query.

I read that CE functions are more optimized.

Can somebodyplease  explain in more detail what is meant by "more optimized"?

Looking forward to your inputs.

Best regards,

Sidharth Mishra

[jose_manuel_molina · Accepted solution]

Hi.

The reason is the following, "CE functions” that can be invoked inside the procedure that are performed directly against the Calculation Engine avoiding the SQL Script Optimizer.

However, the counterpart of CE functions is that they should be mixed with reqular SQL statements, because having that mixture means have to go back and forth between the engines causing performance problems.

Regards.

[Former Member · Accepted solution]

Hi Jose, Thanks for replying.

When i write plain SQL inside the calculation view where does the execution happen?

CE engine or Join engine.?

According to the explanation above plain SQL should get converted and executed in CE engine , i do not see Join engine anywhere.

I did a visualize plan on a SQL based calculated view and it shows everything was executed in Join Engine

My Calc view code:

/********* Begin Procedure Script ************/

BEGIN

SELECT "MKT", "PRODUCT", "BRAND", "CATEGORY", SUM("SALES_UNIT") as "SALES_UNIT", SUM("SALES_DOLLAR") as "SALES_DOLLAR", SUM("AVERAGE_PRICE") as "AVERAGE_PRICE"

FROM "P3_PRICING"."SIDHARTH" GROUP BY "MKT", "PRODUCT", "BRAND", "CATEGORY";

/********* End Procedure Script ************/

I ran the query SELECT * FROM "MYVIEW" from editor.

The visualize plan is below:

All execution happened inside Join engine.

Can you kindly explain what happened here?

Best regards,

Sidharth Mishra

[jose_manuel_molina · Accepted solution]

Hi.

This is the normal behavior.

Calculation Views runs inside the Calculation engine. But depending of the artifacts that are invoked from the calculation, sup-queries are forwared to the JOIN engine or against the OLAP engine.

Try to rewrite the SELECT statement using only CE functions. In that case all queries should be executed by the Calculation Engine.

Regards.

[Former Member · Accepted solution]

Hi Jose,

If i get you correctly.

If we write plain SQL then the execution if transfered to Join engines.

Then coming back to my original question

"Why are CE functions faster than SQL?"

The reason seems to be not the "SQL Script Optimizer".

"The reason is the following, "CE functions” that can be invoked inside the procedure that are performed directly against the Calculation Engine avoiding the SQL Script Optimizer."

Infact i suspect that the performance of SQL is bad because they are not using the CE engine at all. In fact it has a completely different algorithm to achieve the results.

If we look at the execution plan of SQL , there are so many steps before the actual data is retrieved whereas i did the same excercise using CE functions in my calc view  and my plan is below:

What happens inside this last block that makes it so fast?

And why has SAP decided to have different execution strategies for CE functions and SQL?

Looking forward to your inputs.

Best regards,

Sidharth Mishra