Switch Off Language-Specific Sorting to Improve Performance with RDS based OData services

When using the mapped data source approach (which is not recommended anymore) it is possible to manipulate the query options in order to parameterize and fine-tune the execution of your SADL-based SAP Gateway services that have been build using the mapped data source approach.

First of all it has to be mentioned that manipulating the query options requires deep knowledge of the underlying data model and it is therefore usually not recommended to be used.

With the advent of the Referenced Data Source approach SAP recommends to use this approach since it is much easier to handle from an implementation perspective and it leverages the latest developments of the SADL engine.

However, when using the Referenced Data Source approach, it is not possible anymore to fine tune the SADL queries which is usually not required.

There are however certain edge cases where you might want to change the behavior of the SADL runtime, especially when you want to switch off language-specific sorting to improve the performance of selected queries.

Though there is no option to tap into the behvavior of the SADL framework it is possible (but not recommended in all cases) to use the SAP Gateway Framework as an extenstion option.

When building an OData service using the reference data source approach SEGW generates a data provider extension class with the extension “_DPC_EXT” and a model provider extension class with the extension “_MPC_EXT”.

Suppose we have built an OData service based on RDS that is based on the following three CDS views:

  • ZEPM_I_SALESORDER_E
  • ZEPM_I_SalesOrderItem_E
  • ZEPM_I_Stock

When we execute the following request

/sap/opu/odata/SAP/ZTESTRDS_3_SRV/ZEPM_I_SALESRODER_E('500000000')/to_Item?$expand=to_AvlStock&$skip=1&$top=2&$orderby=Product%20asc

it is possible to redefine the method /iwbep/if_mgw_appl_srv_runtime~get_expanded_entityset to intercept any incoming $expand request for expanded entitysets.

This we can do by checking

a) whether the request is targeted to a certain entity set

DATA(entityset_name) = io_tech_request_context->get_entity_set_name( ).

b) whether a certain $expand statement is used

DATA(compare_result_item_toavlstock) = io_expand->compare_to_tech_names( 'TO_AVLSTOCK' ).

c) whether a certain navigation path has been used

DATA(navigation_path) = io_tech_request_context->get_navigation_path( ).

When the following IF statement is true

 IF line_exists( navigation_path[ nav_prop = 'gTO_ITEM' ] ) AND lines( navigation_path ) = 1 AND line_exists( source_keys[ name = 'SALESORDER' ] ) AND lines( source_keys ) = 1 AND entityset_name EQ 'zEPM_I_SalesOrderItem_E' AND ( compare_result_item_toavlstock EQ io_expand->gcs_compare_result-match_subset OR compare_result_item_toavlstock EQ io_expand->gcs_compare_result-match_equals ).
... ELSE. super->/iwbep/if_mgw_appl_srv_runtime~get_expanded_entityse(
... ENDIF.

our code based implementation will be used or the request will be handled by the SADL framework by calling the method

super->/iwbep/if_mgw_appl_srv_runtime~get_expanded_entityset

You can find the complete source code of the class and the CDS views below.

Our code based implementation has to retrieve the data from the underlying CDS views. And it has to fill a data structure of the following nested type.

 TYPES: BEGIN OF t_item_avlstock. INCLUDE TYPE zcl_ztestrds_3_mpc=>ts_zepm_i_salesorderitem_etype. TYPES: to_avlstock TYPE STANDARD TABLE OF zcl_ztestrds_3_mpc=>ts_zepm_i_stocktype WITH DEFAULT KEY, END OF t_item_avlstock. TYPES t_items_avlstock TYPE STANDARD TABLE OF t_item_avlstock.

But since it uses plain Open SQL and not the SADL framework the parameter that would trigger the language dependend select will not be filled as

When checking a trace in ST05 we can see that when using the SADL framework the query contains a WITH PARAMETERS( ‘LOCALE’ = ‘EN’ ) statement.

ZEPM_I_STOCK

SELECT  WHERE “MANDT” = ‘100’ AND “PRODUCT” IN ( ‘HT-1001’ , ‘HT-1002’ ) LIMIT 2147483647  WITH PARAMETERS( ‘LOCALE’ = ‘EN’ )  W

Whereas when using the ABAP implementation

ZEPM_I_STOCK SELECT

<FDA READ>  WHERE “MANDT” = ‘100’ AND “PRODUCT” = ‘HT-1002’   WITH RANGE_RESTRICTION(‘CURRENT’)

this parameter is not present.

CLASS zcl_ztestrds_3_dpc_ext DEFINITION PUBLIC INHERITING FROM zcl_ztestrds_3_dpc CREATE PUBLIC . PUBLIC SECTION. METHODS /iwbep/if_mgw_appl_srv_runtime~get_expanded_entityset REDEFINITION. PROTECTED SECTION. PRIVATE SECTION. METHODS get_osql_orderby_clause IMPORTING !io_tech_request_context TYPE REF TO /iwbep/if_mgw_req_entityset RETURNING VALUE(rv_orderby_clause) TYPE string .
ENDCLASS. CLASS zcl_ztestrds_3_dpc_ext IMPLEMENTATION. METHOD /iwbep/if_mgw_appl_srv_runtime~get_expanded_entityset. "nested result type TYPES: BEGIN OF t_item_avlstock. INCLUDE TYPE zcl_ztestrds_3_mpc=>ts_zepm_i_salesorderitem_etype. TYPES: to_avlstock TYPE STANDARD TABLE OF zcl_ztestrds_3_mpc=>ts_zepm_i_stocktype WITH DEFAULT KEY, END OF t_item_avlstock. TYPES t_items_avlstock TYPE STANDARD TABLE OF t_item_avlstock. "return type of get_skip( ) and get_top( ) is string DATA top TYPE i. DATA skip TYPE i. DATA item_avlstock TYPE t_item_avlstock. DATA items_avlstock TYPE t_items_avlstock. DATA item_key TYPE zcl_ztestrds_3_mpc=>ts_zepm_i_salesorderitem_etype. DATA item_keys TYPE zcl_ztestrds_3_mpc=>tt_zepm_i_salesorderitem_etype. DATA so_key_field TYPE zepm_i_salesorderitem_e-salesorder. DATA so_key TYPE zcl_ztestrds_3_mpc=>ts_zepm_i_salesroder_etype. DATA so_keys TYPE zcl_ztestrds_3_mpc=>tt_zepm_i_salesroder_etype. DATA(entityset_name) = io_tech_request_context->get_entity_set_name( ). DATA(compare_result_item_toavlstock) = io_expand->compare_to_tech_names( 'TO_AVLSTOCK' ). DATA(navigation_path) = io_tech_request_context->get_navigation_path( ). DATA(lt_orderby_string_items) = io_tech_request_context->get_orderby( ). skip = io_tech_request_context->get_skip( ). top = io_tech_request_context->get_top( ). DATA(has_inlinecount) = io_tech_request_context->has_inlinecount( ). DATA(has_count) = io_tech_request_context->has_count( ). DATA(lt_selected_props_so) = io_tech_request_context->get_select_entity_properties( ). " get_selected_properties( IMPORTING et_selected_property = lt_selected_props_so ). CONCATENATE LINES OF lt_selected_props_so INTO DATA(lv_select_string_so) SEPARATED BY ','. DATA(orderby_clause) = get_osql_orderby_clause( io_tech_request_context ). DATA(source_keys) = io_tech_request_context->get_source_keys( ). io_tech_request_context->get_converted_source_keys( IMPORTING es_key_values = item_keys ). IF line_exists( navigation_path[ nav_prop = 'gTO_ITEM' ] ) AND lines( navigation_path ) = 1 AND line_exists( source_keys[ name = 'SALESORDER' ] ) AND lines( source_keys ) = 1 AND entityset_name EQ 'zEPM_I_SalesOrderItem_E' AND ( compare_result_item_toavlstock EQ io_expand->gcs_compare_result-match_subset OR compare_result_item_toavlstock EQ io_expand->gcs_compare_result-match_equals ). "read data for sales order items and stock from the CDS views via openSQL and set the response. CALL FUNCTION 'CONVERSION_EXIT_ALPHA_INPUT' EXPORTING input = source_keys[ 1 ]-value IMPORTING output = so_key_field. " so_key_field = |{ source_keys[ 1 ]-value ALPHA = OUT }|. SELECT * FROM zepm_i_salesorderitem_e WHERE salesorder = @so_key_field ORDER BY (orderby_clause) INTO TABLE @DATA(sales_order_items) UP TO @top ROWS OFFSET @skip. IF has_inlinecount = abap_true. SELECT COUNT( * ) INTO @DATA(count) FROM zepm_i_salesorderitem_e WHERE salesorder = @so_key_field. es_response_context-inlinecount = count. ENDIF. LOOP AT sales_order_items INTO DATA(sales_order_item). item_avlstock = CORRESPONDING #( sales_order_item ). SELECT * FROM zepm_i_stock WHERE product = @sales_order_item-product INTO CORRESPONDING FIELDS OF TABLE @item_avlstock-to_avlstock. APPEND item_avlstock TO items_avlstock. ENDLOOP. copy_data_to_ref( EXPORTING is_data = items_avlstock CHANGING cr_data = er_entityset ). APPEND 'TO_AVLSTOCK' TO et_expanded_tech_clauses. ELSE. super->/iwbep/if_mgw_appl_srv_runtime~get_expanded_entityset( EXPORTING iv_entity_name = iv_entity_name iv_entity_set_name = iv_entity_set_name iv_source_name = iv_source_name it_key_tab = it_key_tab it_navigation_path = it_navigation_path io_expand = io_expand io_tech_request_context = io_tech_request_context IMPORTING er_entityset = er_entityset es_response_context = es_response_context et_expanded_clauses = et_expanded_clauses et_expanded_tech_clauses = et_expanded_tech_clauses ).
*CATCH /iwbep/cx_mgw_busi_exception.
*CATCH /iwbep/cx_mgw_tech_exception. ENDIF. ENDMETHOD. METHOD get_osql_orderby_clause. DATA: lt_orderby TYPE /iwbep/t_mgw_tech_order, ls_orderby TYPE /iwbep/s_mgw_tech_order, lv_property TYPE string, lv_asc_desc TYPE string. CLEAR rv_orderby_clause. lt_orderby = io_tech_request_context->get_orderby( ). LOOP AT lt_orderby INTO ls_orderby. CLEAR lv_property. IF ls_orderby-property IS NOT INITIAL. lv_property = ls_orderby-property. ELSEIF ls_orderby-property_path IS NOT INITIAL. lv_property = ls_orderby-property_path. ENDIF. IF ls_orderby-order EQ 'desc'. lv_asc_desc = 'DESCENDING'. ELSE. lv_asc_desc = 'ASCENDING'. ENDIF. IF lv_property IS NOT INITIAL. CONCATENATE rv_orderby_clause lv_property lv_asc_desc INTO rv_orderby_clause SEPARATED BY space. ENDIF. ENDLOOP. CONDENSE rv_orderby_clause. ENDMETHOD. ENDCLASS.

ZEPM_I_SALESORDER_E

@AbapCatalog.viewEnhancementCategory: [#NONE]
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Dummy for Store'
@Metadata.ignorePropagatedAnnotations: true
@ObjectModel.usageType:{ serviceQuality: #X, sizeCategory: #S, dataClass: #MIXED
} @OData.publish: true define view entity ZEPM_I_SALESORDER_E as select from SEPM_I_SalesOrder_E association [0..*] to zEPM_I_SalesOrderItem_E as _Item on $projection.SalesOrder = _Item.SalesOrder { key SalesOrder, CreatedByUser, CreationDateTime, LastChangedByUser, LastChangedDateTime, IsCreatedByBusinessPartner, IsLastChangedByBusinessPartner, Customer, CustomerContact, TransactionCurrency, @Semantics.amount.currencyCode: 'TransactionCurrency' GrossAmountInTransacCurrency, @Semantics.amount.currencyCode: 'TransactionCurrency' NetAmountInTransactionCurrency, @Semantics.amount.currencyCode: 'TransactionCurrency' TaxAmountInTransactionCurrency, SalesOrderLifeCycleStatus, SalesOrderBillingStatus, SalesOrderDeliveryStatus, SalesOrderOverallStatus, Opportunity, SalesOrderPaymentMethod, SalesOrderPaymentTerms, BillToParty, BillToPartyRole, ShipToParty, ShipToPartyRole, /* Associations */ _Item
}

zEPM_I_SalesOrderItem_E

@AbapCatalog.viewEnhancementCategory: [#NONE]
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Items'
@Metadata.ignorePropagatedAnnotations: true
@ObjectModel.usageType:{
serviceQuality: #X,
sizeCategory: #S,
dataClass: #MIXED
}
define view entity zEPM_I_SalesOrderItem_E
as select from SEPM_I_SalesOrderItem_E
association [0..*] to ZEPM_I_Stock as _AvlStock on $projection.Product = _AvlStock.Product
{
key SalesOrder,
key SalesOrderItem,
Product,
TransactionCurrency,
@Semantics.amount.currencyCode: 'TransactionCurrency'
GrossAmountInTransacCurrency,
@Semantics.amount.currencyCode: 'TransactionCurrency'
NetAmountInTransactionCurrency,
@Semantics.amount.currencyCode: 'TransactionCurrency'
TaxAmountInTransactionCurrency,
ProductAvailabilityStatus,
OpportunityItem,
/* Associations */
_Product,
// _ProductAvailabilityStatus,
// _SalesOrder,
// _ScheduleLine,
// _Text,
// _TransactionCurrency,
_AvlStock
}

ZEPM_I_Stock

@AbapCatalog.viewEnhancementCategory: [#NONE]
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Stock'
@Metadata.ignorePropagatedAnnotations: true
@ObjectModel.usageType:{ serviceQuality: #X, sizeCategory: #S, dataClass: #MIXED
}
define view entity ZEPM_I_Stock as select from SEPM_I_Stock { key StockUUID, _Product.Product as Product, ProductUUID, OrganizationalUnitUUID, Bin, QuantityUnit, @Semantics.quantity.unitOfMeasure: 'QuantityUnit' Quantity, @Semantics.quantity.unitOfMeasure: 'QuantityUnit' LotSizeQuantity, @Semantics.quantity.unitOfMeasure: 'QuantityUnit' MinimumStockQuantity, /* Associations */
// _OrganizationalUnit, _Product, _QuantityUnit
}