Case...when for CURRENCY_CONVERSION function

[eugene_517]

Hi guys, recently, I encountered a strange issue. I have an analytic CDS query to derive records from VBAP, the currency conversion function bring heavy performance issue. I added 'Case ... when' in CDS view and compared the input currency and item currency, if they were same, returned the amount directly without conversion. And in test system, we had 800,000+ VBAP records. ( EUR had 700,000+ records, USD had 100,000 records) . I tested the performance in HANA studio (SQL) and GW client (server request) by passing EUR and USD, there was no different. Logically, if passing 'EUR', it should be faster as it has less records to do the conversion. eh...., not sure how the HANA works with it. I really appreciate if anyone has any experience or idea about this case. Thanks in advance!

[lbreddemann]

Hey eugene_517,

thanks for taking the time to come back with your results. Much appreciated.

It's good to hear that you were able to improve the performance of the query by splitting it up into two disjunct branches: one where conversion is not required (as the data is already in display currency ) and a second one for where conversion is required (data is not in display currency).

Now, this specific pattern is rather common for queries in SAP systems; SAP HANA should understand this pattern and automatically optimize accordingly.

Leaving this to the SQL/CDS developer is not a good solution as it is trivial to make errors in the separation of the two branches, leading to tuples that may be present in both of the branches. This would mean wrong results.

Also, it pushes an optimisation decision (when is it better/faster to split the two data sets and when is it better to just do a single set of data) to the design level; that is precisely what SQL optimisers should prevent.

I would suggest to SAP HANA development to consider automating this optimisation. There are plenty of options to do this. One simple way could be to extend the currency_conversion function with a list of conditions - similar to the ones in the case clause - to split the data into two.

The optimiser could use these "split-conditions" at execution time to estimate how large each group is likely going to be and decide the actual execution based on that.

This would also make for a much cleaner SQL/CDS statements as the cumbersome CASE construct could be removed.

Cheers.

Lars

[Jörg_Brandeis · Accepted solution]

Hi Eugene,

to find out the truth, you have to analyze the execution plan. But I have some suspicions.

I think it is due to the fact that the SQL function CONVERT_CURRENCIES in SQLScript still internally calls the obsolete CE function CE_CONVERSION. This function works on table level and not on row level. See SAP documentation.

If this suspicion is correct, you could separate the table into two halves:

• One where both currencies are the same and
• The other with the rest of the records, where you can apply the CONVERT_CURRENCIES

And I would merge them again at the end with a UNION_ALL.

If you try this, please post the result! It would be interesting to see if this was successful.

Regards,
Jörg

[eugene_517 · Accepted solution]

Hi Brandeis,

Thanks a lot for your analysis, I really appreciate it. I tried your idea and compared the performance. It did be more faster, the performance was improved about 25%~30%. Currently, I guess this might be the way to improve the performance for this case. The behavior of SAP HANA SQL execution is very confusing. Anyway, thanks for you great idea.

[lbreddemann]

I'm not sure that a different internal implementation of the conversion function would change all that much here.

One part of the answer to this "mystery" is that the CASE WHEN clause is a declaration for how data should be handled and not the exact instruction for how the DB has to perform this.

Practically speaking, this means, that the whole set of tuples that remains after applying the WHERE clause is fed into the conversion function. Unwanted results (the data that matches the other WHEN branches) is later discarded.

On the plus side, the conversion itself works on a whole set of records at once and not just row-by-row. So, the execution speed difference between e.g. 100 and 10000 records should barely be noticeable. But 100k to 700k difference means many more "chunks" to work through.

I would not recommend working around this by putting in two different code paths with UNION. This likely will introduce another bottleneck (materialisation) and complicate the CDS view a lot.

Instead, try and filter the data as much as possible before the conversion (WHERE clause). No frontend facing view should return 100k+ records.

[eugene_517]

Hi Lars,

Thanks a lot for you explanation. It's really helpful. I tried Brandies way to separate the table into two halves based on currency, and union them together. Due to my comparation, the performance gets improved about 25%~30%. I looked deep into the execution plan, the time cost of the 'Calculation Model' did be reduced due to the less number of the records to be converted I guess.