Discovery of the speed on HANA, a practical approach
Hi,
I’ve been wanting to write a blog for quite some time. But haven’t really found the proper subject to write about. I have finally managed to create a quite simple example, where it is quite obvious that you really should consider the way you are writing your ABAP code to utilize the power of SAP HANA.
I used the data generator for the EPM model to create some proper test data, you can do this yourself by using transaction SEPM_DG. I created 500.000 sales orders.
My test scenario is by using a simple class that is using an AMDP procedure and the CE function CE_CONVERSION to convert a currency from EUR to USD. I use a report to call this procedure and then output the result. The subset i send to the AMDP procedure was a table with columns for id, amount, source unit, target unit and reference date.
My initial test showed a not very impressive result of HANA. I had done a select * into an internal table and looped through that. There was a reason why SAP is warning against this method. The result was that the report run for over 30 seconds to process an output. Here is the runtime analysis from SE30
I tested this report both by using a functional module CONVERT_TO_LOCAL_CURRENCY for the currency conversion and using the CE_CONVERSION amdp procedure. The result was a difference in 1 second.
So after being rather depressed by this I decided to optimize my code a bit. Firstly i did a proper select by only choosing the columns that i needed. This resulted in a performance improvements of 10 seconds.
However the difference between using a functional module and an AMDP procedure was still minimal. However i did achieve a 2 seconds improvement by only sending down the ID, amount and source unit and then providing a single value for reference date and target unit.
But this didn’t really impress much did it?
Then i remembered this fine drawing.
So what if i pushed my select statement into the AMDP procedure as well?
Well the result speaks for itself, i achieved a performance improvement of 50% and by a massive 66% from my initial starting point.
The reason for this improvement is obviously that i have pushed the entire logic into the database, so i am calling the AMDP procedure and getting the resultset back with the sales orders and the converted amounts.
So what can we learn from this?
- NEVER use select * on SAP HANA
- Push as much logic into the database as possible. Create a connector class to execute SQLSCRIPT in ABAP or create a view or stored procedure to consume in ABAP instead of using selects on the application layer
- If you need to send a subset to be handled on the database layer, then really consider the size of the subset and how many columns you are sending. SIZE DOES MATTER!!!
I hope this read has providing a minor insight into developing on HANA. I’m still learning and i might be back with more information 🙂
NB: you can view my source code here