SQL vs CE Functions Performance

[former_member314446]

Hello,

I understand the point that we should now be using SQL statements over CE Functions.  The system I am using is on SP10 and I have come across a big performance gap between the two methods above and am trying to figure out why this could be in a scripted calculation view ( select all records from calculation view).  Also to note, there is much more to this code but I have limited it to this specific piece.

CE Function

var_out = CE_CALC_VIEW("<CALC_VIEW>")

When using this, I get the output in approx. 12 seconds.

SQL

var_out = SELECT * FROM <CALC_VIEW>

When using this, I get the output in approx. 2min 30 seconds.  I also get about the same time using the graphical calculation view.

Does anyone have any ideas on what might be causing this?

Thanks,

Travis

[lbreddemann · Accepted solution]

What does the called calc view look like?

Are the different execution times reproducible or does this happen only with the first call of each method?

The point here is that, depending on what happens in the calc view, it might be unrolled into the overall query path of your stored procedure with the SQL approach.

Typically this provides more options for optimization; but sometimes more options to change something also means more options to go wrong.

For more details on this I recommend to look into what each version does via PlanViz.

[former_member314446 · Accepted solution]

Hello Lars,

Thanks for the reply. To start, the execution times are reproducible.

I discovered that the underlying calculation view (graphical) was the issue.  Specifically there is modeling in there which will generate 12 records (1 for each month), for each record. The requirement is that if there is only a transaction for a particular dimension combination in 01.2015, it should show a record for the other 11 months with null measure's.  To accomplish this there were 12 projection created which then flow into a union.

From the plan viz with the SQL statement, I can see that for each of the 12 projections, it is executing from the base tables 12 times. Also to note, some of the base tables are virtual tables which are accounting for the higher selection times.  Therefore I am basically getting a 12x performance increase (~2min30sec).

With the CE function, it does not seem to be executing to the base tables each time, only once, therefore I am getting an execution time of (~12sec).

I am thinking I may have to come up with a more optimized way to do the record generation so it does not multiply by 12.  Any suggestions/thoughts?

Thanks,
Travis

[lbreddemann · Accepted solution]

Based on the description you provided, there is not much I can propose as it is still all too vague.

Anyhow, it sounds like some sort of semantical pruning would help here.

Have you tried to use the constant-union-filter-approach to enable input source pruning of the different data sources?

As you've had the treat to attend TechED 2015, I recommend to revisit Former Member's session DMM208: New and Best Practices for Data Modeling with SAP HANA | SAP TechEd Online&lt;/title&gt;&lt... (http://www.sapevents.edgesuite.net/TechEd/TechEd_Vegas2015/pdfs/DMM208.pdf)

Before you ask "why can CE function do that automatically and SQL cannot?": I haven't seen your models, so I really don't know what's happening with those.

Generally speaking, the CE functions cannot do anything "more" or "better" (rather the other way around).

If this doesn't help you all that much, as an SAP employee you always have the option to address this topic internally.

Cheers,

Lars

[former_member314446 · Accepted solution]

Thanks again for the information, sorry for the vagueness.  As I was already using the constant union, I made some adjustments to the underlying views, especially around the record generation modeling.  When using the SQL, the time decreased by about 88% from the original and is similar to the CE function performance, while still having some others areas the could be optimized.

Thanks,

Travis