Code Push Down for HANA Starts with ABAP Open SQL

[sundaresan_k]

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

1. The column list needs to be separated by commas
2. 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 :smile:

Old is Gold but New is Platinum :smile:

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?

1. Your ABAP code will remain database agnostic and will run on any ABAP server independent what is the underlying database
2. You implicitly take advantage of all transparent optimizations that have been achieved in the Database Interface level
3. 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

1. Let us first get started :smile: 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.
2. Use aggregate functions where relevant instead of doing the aggregations in the ABAP layer
3. Use arithmetic and string expressions within Open SQL statements instead of looping over the data fetched to do the arithmetic and string operations
4. Use computed columns in order to push down computations that would otherwise be done in a long loops
5. 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" :cool:

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!!! :smile:

Tell me, and I will forget. Show me and I may remember. Involve me, and I will understand.” Confucius