Code Push Down for HANA Starts with ABAP Open SQL
What is Code Push Down?
One of the key differences for developing applications in ABAP for HANA is that you can push down data intense computations and calculations to the HANA DB layer instead bringing all the data to the ABAP layer and the processing the data to do computations. This is what is termed as Code-to-Data paradigm in the context of developing ABAP applications optimized for HANA.
Where does Code Push Down Start?
It is a general misconception that if one wants to do code push down in ABAP for HANA you always need to either use HANA native SQL or build complex HANA artefacts in order to achieve this.
But in reality the Code Push Down for HANA from ABAP can very well start with ABAP Open SQL. Let us see How and Why?
The New Enhanced Open SQL
It has been SAP’s constant endeavour to improve Open SQL with each release of ABAP Application Server in order make it the most efficient channel for accessing data in a manner that is database agnostic.
Since the release of NW AS ABAP 7.4 SP5 this attempt has manifested in the form several cool and advanced features letting go of many earlier limitations from open SQL
“Change is the Only Constant” – Heraclitus
In order to make use the advanced features of Open SQL one needs to accommodate a minor change in the way we have been writing Open SQL statement for a while now.
SELECT so_id AS sales_order_no, currency_code, gross_amount, FROM snwd_so INTO TABLE @lt_so
As shown in the above code snippet the most significant changes are the following in terms of the syntax
- The column list needs to be separated by commas
- The host variables used within the open SQL statement needs to be escaped by an “@” symbol
In order to extend the existing functionalities of open SQL with new features a minor change had to be incorporated with the syntax hence this change.
But for sure we all would agree that positive changes are always welcome 🙂
Old is Gold but New is Platinum 🙂
The existing Open SQL statements with the old syntax will remain intact and syntactically valid. But if you want to make use of some of the advanced features of the new Open SQL you need start using the new syntax. Below is a summary(but not a complete list) of some of the new features supported in Open SQL,
Extended features supported by the new Open SQL
Following are a couple of new features supported by extended Open SQL syntax
- Support for arithmetic expressions and computed columns in the projection list.
Remember the days when you need to loop through the internal table fetched using the Open SQL statement in order to compute/derive the value for an additional column. You don’t have to do that any more. You can have computed columns part of the projection list.
- Support for string expressions part of the project list
You can now have string expression with in your projection , for instance to create new concatenated column using columns from the tables accessed
This is only the tip of the ice berg for a more comprhensive list of all new features supported by the latest Open SQL syntax refer to the following link
Code Push Down for HANA using Open SQL
As it is already evident with the extended syntax for Open SQL , the new Open SQL still remains preferred approach for Code Push Down to HANA and it very diligently augments and supports the other optimization techniques like AMDP and CDS.
What are the advantages of using Open SQL to do code push down?
- Your ABAP code will remain database agnostic and will run on any ABAP server independent what is the underlying database
- You implicitly take advantage of all transparent optimizations that have been achieved in the Database Interface level
- All default performance optimizations like use of buffer and house keeping activities like client handling etc are automatically taken care of
5 Ways of achieving Code Push Down to HANA from ABAP using Open SQL
- Let us first get started 🙂 Start using the new Open SQL syntax. Remember it is easy to convert your existing Open SQL statements to new syntax without any side effects.
- Use aggregate functions where relevant instead of doing the aggregations in the ABAP layer
- Use arithmetic and string expressions within Open SQL statements instead of looping over the data fetched to do the arithmetic and string operations
- Use computed columns in order to push down computations that would otherwise be done in a long loops
- Use CASE and/or IF..ELSE expressions within the Open SQL in order embed the logic of conditional expression which in the past would have been done after fetching the results from the database.
If you want to play around with the new Open SQL syntax we have cool new feature in the latest released version of ABAP in Eclipse called “SQL Console”. To know more about this feature refer to my colleague Vijayan Balasubramanian blog on the new SQL Console in AiE here.
Get “Involved” 😎
Do you want to have the first hand experience of the latest Open SQL features in addition to other exciting features for ABAP Development for SAP HANA? If you say yes, why do you wait?
Get enrolled for the Open SAP course on ABAP Development for SAP HANA and get “involved” and be part of the exciting journey.
Wishing you loads of fun!!! 🙂
Thank you for the detailed article. Do you have an end-to-end example of how can we convert a existing custom report into HANA using the code push down methodology.
We currently are running ECC on HANA and I am trying to convert an existing Z report into HANA.
This is really informative.
Hey Sundaresan, very nice, thanks for opening my eyes in that respect!
So the lesson learned here is: if you formulate your Open-SQL SELECT so it returns the value / format you want, you don’t have to do any post-processing in ABAP.
Here’s a little, very simple use-case I have imagined:
I want list of sales-orders and who created them:
…apparently, some usernames where created with a company prefix, others not.
If there is such a prefix, I want to drop it, leaving over only the name.
This could be how:
(OPEN SQL-Console in AdT is great for trying this out!)
I am happy that you liked the post and were able to relate it to a sample use case that you have demonstrated.
Excellent blog. I wish I would have found it quicker. I've also had fun learning that select * isn't a bad thing anymore when using CDS. That was a big surprise for me. When I get a chance I'm going to click through the links.
This blog is even helpful around 2 years later! We are catching up.
Yes, and I think that is true form some other blogs as well. I don't have an example handy, but I like the "Related Blog Posts"-section on the right -> that's how I re-discovered this today! 😉
Excellent blog. Thank you for sharing.
Again, coming back to this blog, as the topic is still relevant and comes up from time to time.
[ Interesting: Searching for "abap code pushdown begins with open sql" in SAP Community search did NOT help me find this;
in Google, it was the 1st hit! ]
Those are more examples of simple use cases, surly not new but helpful as a reference:
Thanks Joachim for the feedback. I am glad this blog is still useful. I will check and add your inputs part of the main blog. Is that ok with you?
Sure, if you see benefit in including parts if my comment in the main blog, that's very ok with me!