HANA for ABAP – Scope for improvement
I had been reading a couple of blogs on SAP HANA concept and technology and I really appreciate them.
“RAM is faster than hard disk” – This is the idea behind using In-Memory Database concept HANA.
I also read a couple of blogs on how to use HANA for the common ABAP developer. This approach uses the concept of ‘Secondary Database Connection’. So basically we need to slightly modify the SQL statements and add the suffix e.g. CONNECTION ‘ZHANADB’ to be able to use HANA. (Assuming the name of secondary database connection is ZHANADB).
I have some thoughts on this which I would like to share.
- Let us say I already have an existing custom report which I would like to make ‘HANA enabled’, then what do I need to do? I need to tamper inside this report most of the SQL statements, if not all, and add the suffix e.g. ‘CONNECTION ZHANADB’. This has to be done manually for each of the SQLs. So if there are 10 important SQLs inside the report, I need to change at 10 locations. So far so fine.
- Continuing the above example, let us say there are some important and heavy-duty custom Function Modules which have been called inside the report to fetch important data. What happens to them? They will still point to the existing SAP database and not HANA. So my report is not yet fully ‘HANA enabled’.
- Continuing the above example, I need to decide whether I should modify the SQLs inside those FMs to make them also HANA enabled. There is a probability that those custom FM’s are used in other reports and programs. Hence I need to take a call whether I should really HANA enable those custom FMS, or copy them to create new ones and use those new ones inside my custom report.
- Continuing the above example, let us say instead of custom FMs, I have used standard FMs in my report. Now what to do with them? Obviously I cannot ‘REPAIR’ them since they are standard FMs and I would need object key for the ‘REPAIR’. Moreover it is not recommended. So in such cases my report is still not fully HANA enabled.
- Continuing the above example, and considering point #1, what happens if a report is already HANA enabled in production and now I need to do some additions in it. Let us consider the change request is given to a new developer. There is a probability that this new developer is not aware that this report is already HANA enabled. Hence there is a chance that he may write additional SQLs but without using HANA concept. So in such case still my report is not fully HANA enabled.
- Continuing the above example, let us say everything is done and the report is in production environment. The management now asks ‘HOW MUCH TIME we have saved in this report?’. Now, how do I actually find out the difference in performance in terms of seconds/minutes/hours once my report is there in production environment? The report will always run against HANA since the SQLs have been HARDCODED with the suffix. There is no way to compare unless and until there is another copy of the same program, but without using the ‘CONNECTION’ suffix in the SQLs. If such program is there, I need to run them separately and note down the time for comparison. But such approach of keeping two copies of the program in not recommended.
Keeping in mind the above points, it makes me feel that ‘Approach for using HANA for ABAP is not yet mature. The current approach is a kind of workaround’.
To make the approach a little professional and manageable, there are some things which can be thought of in the newer versions of SAP.
a) a) There should be some configuration which TAGS particular reports or transaction codes to allow execution against the secondary HANA database ALSO. This means no hard coding of SQLs inside the report with the suffix ‘CONNECTION’.
The above configuration for the particular report, will determine at run time whether the SQLs are executed against HANA database or local SAP database.
E.g. If there is a transaction ZREP1 which has been tagged for HANA, with default ‘HANA’, then
ZREP1 -> This will run with HANA
ZREP1 SAP -> This will run with local SAP database. (The SAP suffix is a kind of parameter we can provide something similar we provide for command-line parameters)
Thus, if there is something like above in point (a), then following BENEFITS can be achieved:
1. 1. No HARD-CODING for each SQL
2. 2. Zero modification in code of existing custom reports
3. 3. Easily make existing reports ‘HANA enabled’
4. 4. No REPAIR required for making standard FM/Report HANA enabled.
5. 5. Real time performance comparison of reports for HANA v/s Standard Database
>(Assuming the name of SM30 connection is ZHANADB
Secondary database connections do not use SM30 Destinations. They are configured in DBACOCKPIT and stored in table DBCON.
>This means no hard coding of SQLs inside the report with the suffix ‘CONNECTION’.
You don't have to hard code the destination. It can be provided with a variable. If the variable is initial, the primary DBMS connection will be used. This allows you to run the report both ways in production and tell exactly what the performance different will be.
>There should be some configuration which TAGS particular reports or transaction codes
Actually we have this in pilot usage for Max Attention customers. We call it Kernel Switch. You can configure a certain table/report combination and all read only SQL statements in that combination are automatically redirected to the secondary database connection with no code changes.
It is important to note that the secondary database connection (using either of the above approaches) is designed as a temporary solution. Ultimately you will be able run HANA as the primary DBMS of your ERP system and accelerate all your applications without any code changes. We will also enhance ABAP syntax and data dictionary in a future NetWeaver Enhancement Package to have additional HANA-Specific functionality. However until those points, you can use the Secondary Database Connection as a way to ease into a HANA implementation. You can use it for a small area where you have a particular need for HANA's speed.
In some areas SAP has already done these changes in standard. These are the ABAP Accelerators like CO-PA Accelerator. Customers.
The other thing to consider is that just redirecting existing SQL statements to HANA isn't going to maximize you performance. Often SQL statements need to be rewritten (Inner Joins instead of ABAP internal table loops). We see in the Accelerators we often call HANA Database Procedures or Analytic Views instead of just small changes to the existing SQL.
You can address some immediate problems - IE low hanging fruit - with the secondary database connection approach.