Gateway Service: Optimizing Performance with SQL Paging

[JK]

I did some Performance Analysis of Gateway Services. My Focus in this BLOG is on GET_ENTITYSET Methods with a High Number of Database Entries selected, for instance a lot of salesorders etc. where it is  for performance reasons not possible to load all data in the initial request to the client/Browser and avoid further calls of GET Method, so further data is loaded when the users scrolls down or change filters. because the service is stateless, i've found a lot of expensive sql statements in the trace.

as of Gateway 2.0 there will be a 'limited stateful' mode called soft-state, see link: http://scn.sap.com/docs/DOC-58760

but stateful gives other issues (ressources/memory), and REST was designed stateless (see note 1986626)

when looking at some standard fiori gateway services like salesorder, this was done like this:

CL_LORD_MY_QUOTATION_DPC_EXT

QUOTATIONSET_GET_ENTITYSET

  METHOD quotationset_get_entityset.
...
"initialize paging, if top is not provided 0 is passed.
"no need to check skip,top. They are expected to be numbers. ODATA Gateway checks and provides us
"numeric data
IF is_paging-top IS NOT INITIAL.
lv_max = is_paging-skip + is_paging-top.
ENDIF.

...

SELECT head~vbeln head~auart head~kunnr AS kunag soldto~name1 AS kunag_t head~angdt head~bnddt head~erdat AS erdat_r head~netwr AS netwr_r head~waerk status~gbstk
sdbusiness~bstkd head~vkorg head~vtweg head~spart head~vdatu status~abstk status~rfstk status~uvals status~uvall
UP TO lv_max ROWS
INTO CORRESPONDING FIELDS OF TABLE lt_docs
FROM (lv_from_clause)
WHERE head~vbeln IN lt_rg_vbeln

...

  METHOD truncate_table.

IF iv_skip IS NOT INITIAL.
DELETE ct_table TO iv_skip.
ENDIF.

IF iv_top IS NOT INITIAL.
DELETE ct_table FROM iv_top + 1.
ENDIF.

ENDMETHOD

.

so the good thing is, only the first n (50) entries are selected, but after several scroll requests, this wil be 1000 and more. ok at least the db query buffer is filled on the second select statement, but still not very db-optimized.

regarding db-optimized: i looked at some hana-fioris (XS Server) and they are using SELECT LIMIT 50 OFFSET n Statement, so they can select the DB-Frame of Records required, very nice!

This would be nice for ABAP as well. I hoped that the LIMIT/OFFSET feature would be available with the NEW OPEN SQL Expressions, but it did not (yet) work

of course i can use NATIVE-SQL to use the feature, with the use of the CL_SQL_STATEMENT-Class: (also with Non-Hana Databases)

****Create the SQL Connection and pass in the DBCON ID to state which Database Connection will be used 

  DATA lr_sql TYPE REF TO cl_sql_statement. 

  CREATE OBJECT lr_sql 

    EXPORTING 

      con_ref = cl_sql_connection=>get_connection( 'AB1' ). 

****Execute a query, passing in the query string and receiving a result set object 

  DATA lr_result TYPE REF TO cl_sql_result_set. 

  lr_result = lr_sql->execute_query( 

    |SELECT * FROM SFLIGHT WHERE MANDT = { sy-mandt } AND CARRID = 'LH' limit 200 offset 0 | ). 

****All data (parameters in, results sets back) is done via data references 

  DATA lr_sflight TYPE REF TO data. 

  GET REFERENCE OF lt_sflight INTO lr_sflight. 

****Get the result data set back into our ABAP internal table 

  lr_result->set_param_table( lr_sflight ). 

  lr_result->next_package( ). 

  lr_result->close( ). 

Then i remember the new ALV IDA with Integrated Data Access and here i see a nice feature:  (Also with non-Hana Databases)

data:

IS_RESTRICTIONS Type  IF_SADL_QUERY_ENGINE_TYPES=>TY_RESTRICTIONS,

IS_AGGREGATION  Type  IF_SADL_QUERY_ENGINE_TYPES=>TY_AGGREGATION,

IT_SORT_ELEMENTS Type  IF_SADL_QUERY_ENGINE_TYPES=>TT_SORT_ELEMENTS,

IS_REQUESTED  Type  IF_SADL_QUERY_ENGINE_TYPES=>TY_REQUESTED,

IS_PAGING Type  IF_SADL_QUERY_ENGINE_TYPES=>TY_PAGING,

IS_PARAMETERS Type  IF_SADL_QUERY_ENGINE_TYPES=>TY_PARAMETERS,

EV_NUMBER_HITS  Type  I,

EV_NUMBER_ALL_HITS  Type  I.

data: row_count type i.

data: it_ranges type IF_SALV_SERVICE_TYPES=>YT_NAMED_RANGES.

data: wa_range like line of it_ranges.

data: lt_sbook type table of sbook.

DATA ms_view_metadata TYPE if_sadl_view_db=>ty_view_metadata.

   cl_salv_ida_services=>create_entity_and_abqi(

         exporting iv_entity_id   = conv #( 'SBOOK' )

                   iv_entity_type = cl_sadl_entity_factory=>co_type-ddic_table_view

         importing eo_entity      = data(lo_entity)

                   eo_abqi        = data(lo_abqi) ).

    data(lo_ida_structdescr) = cl_salv_ida_structdescr=>create_for_sadl_entity(

         io_entity = lo_entity ).

*        io_calc_field_handler = io_calc_field_handler ).

     data(lo_query_engine) = new cl_salv_ida_query_engine( io_structdescr_prov   = lo_ida_structdescr

                                                           io_sadl_engine        = lo_abqi ).

     data(lo_idas) = cl_salv_ida_services=>create( io_structdescr_prov  = lo_ida_structdescr

                                                   io_query_engine       = lo_query_engine ).

is_paging-start_row = 10.

is_paging-maximum_rows = 20.

refresh it_ranges.

wa_range-name = 'CARRID'.

wa_range-option = 'EQ'.

wa_range-sign = 'I'.

wa_range-low = 'LH'.

append wa_range to it_ranges.

lo_idas->get_query_engine( )->set_selection_range_tab( it_ranges = it_ranges ).

LO_ABQI->select(

               EXPORTING "is_text_search   = ls_text_search

                         "is_aggregation   = VALUE #( count_alias = l_count_alias )

                         is_requested     = VALUE #( "fill_number_all_hits = abap_FALSE

                                                     "elements = t_group_by_fields

                                                      elements = VALUE #( ( `CARRID`  ) ( `FLDATE`  ) ) fill_data = abap_true )

                         "is_parameters    = ms_parameters

         is_paging            = is_paging

               IMPORTING "ev_number_all_hits = row_count ).

                         et_DATA_ROWS         = LT_SBOOK ).

finally, the IDA does nothing else but calling CL_SQL_STATEMENT with SELECT LIMIT n OFFSET x

i hope you also can use this 🙂