Case...when for CURRENCY_CONVERSION function

[lbreddemann]

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

[Joerg_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.