Introduction

With this blog , I wanted to share some aspects of pagination that I have come across from Gateway projects.

Pagination

Is the process of showing only selected records from the list of total records . Typical example would be our Word Processor “Microsoft Word” which has page breaks inside document.The bottom pages are shown only on demand . I am trying to analyse pagination from a Gateway point of view and the impact on performance

As initial step I have set up some data

Data setup

I have two tables, one an employee table and other a text table for that employee table

Employee table

Emp_table.jpg

Employee text table

EMp_text_table.jpg

I also maintained some dummy values for both English and Deutsche .. Forgive me for my linguistic blunders 😆 .

Employee table entries

/wp-content/uploads/2015/07/emp_table_entries_739238.jpg

Employee text table entries

/wp-content/uploads/2015/07/emp_text_table_entries_739239.jpg

We have two types of Pagination

1 Client side pagination

Here the client calls the shot . The client decides the number of records to be loaded . It does not mean the client fetches all the records from the backend ,buffers it  in client and shows the records as per user request(drag down).

Not so good approach

I have seen in projects which implement this kind of pagination in which the entire records are fetched from the backend. The records are then filtered in the gateway layer. This is worst hit in the perfomance of the UI application. These can happen in case of standard BAPIs also where there is no option to restrict the maximum number of records (No iv_max parameters). The only option is to apply filter criteria to the set of records.

We will try to simulate this ‘Not so good approach’ by creating  an FM API which fetches the all the records from these above tables

FM_GET_EMP.jpg

In this case there is no iv_max or upto parameter in the interface of FM.

We will try mapping the FM to Entity and execute it from the URL.

EMP_MAP.jpg

As  you know in this case, the  DPC class code gets automatically generated for this query . If you have a look at the corresponding entities GET_ENTITYSET method.This is doing pagination after fetching all the records from DB. 🙁 . .

Pagination_bad.jpg

The matter becomes worse when you have multiple sort parameters also to handle. Here you should take care of the sequence in which you do ordering and pagination . First sorting and then pagination otherwise you will get weird results.Moreover Ithe sorting part is not automatically handled by the framework method.So if I run the below URLs in the GW client

1 /sap/opu/odata/SAP/ZARS_PAGE_SRV/EmployeeSet?$skip=2&$top=2

I get correct results (even though performance is not good)

GW_Client_rsult.jpg

2  /sap/opu/odata/SAP/ZARS_PAGE_SRV/EmployeeSet?$skip=2&$top=2&$orderby=Id desc

This will still yield the same results …

You need to explicitly handle the sorting like  what is mentioned in the below blog ..

SAP GW – Implement a better OrderBy for Cust EXT Class

I am copying that piece of code over here










  1. METHOD orderby. 
  2.    DATA: lt_otab   TYPE abap_sortorder_tab, 
  3.               ls_oline  TYPE abap_sortorder. 
  4.    DATA: ls_order LIKE LINE OF it_order. 
  5.    CONSTANTS:BEGIN OF lcs_sorting_order, 
  6.                              descending TYPE string VALUE ‘desc’, 
  7.                             ascending  TYPE string VALUE ‘asc’, 
  8.              END OF   lcs_sorting_order. 
  9. LOOP AT it_order INTO ls_order. 
  10.      ls_otab-name = ls_order-property. 
  11.      IF ls_order-order = lcs_sorting_order-descending. 
  12.        ls_otab-descending = abap_true. 
  13.      ELSE. 
  14.        ls_otab-descending = abap_false. 
  15.      ENDIF. 
  16.      APPEND ls_otab TO lt_otab. 
  17. ENDLOOP. 
  18.    SORT ct_data BY (lt_otab). 
  19. ENDMETHOD. 

PS: The it_order(from io_tech_request_context) which is mentioned in the blog contains ABAP fieldnames of gateway properties. In most cases It is different from the abap backend API result fields.

For eg.I have changed the Abap field name of GW property Id to EMPLOYEEID.

/wp-content/uploads/2015/07/abap_field_name_739765.jpg

This will trigger a corresponding change in the DPC code

/wp-content/uploads/2015/07/abap_field_name1_739778.jpg

So in this case if we have to handle sorting along with pagination . Then you have to do the following steps

1 Loop thru the backend internal table to fill et_entityset(id-EmployeeId) mapping

2 Pass et_entityset to sorting utility method (as mentioned in the blog) to get back the sorted table

3 Do the pagination using lv_skip and lv_top parameters

The sample code is as below


*  - Map properties from the backend to the Gateway output response table -
data:lt_entityset_bfr_paging TYPE zcl_zars_page_mpc=>tt_employee.
  LOOP AT et_data INTO ls_et_data.
*  Provide the response entries according to the Top and Skip parameters that were provided at runtime
*    *  Only fields that were mapped will be delivered to the response table
    ls_gw_et_data-text = ls_et_data-text.
    ls_gw_et_data-name = ls_et_data-name.
    ls_gw_et_data-employeeid = ls_et_data-id.
    APPEND ls_gw_et_data TO lt_entityset_bfr_paging.
    CLEAR ls_gw_et_data.
  ENDLOOP.
      lt_tech_order = io_tech_request_context->get_orderby( ).
**Now pass this to Util method for sorting
LOOP AT lt_tech_order INTO ls_tech_order.
      ls_otab-name = ls_tech_order-property.
      IF ls_tech_order-order = lcs_sorting_order-descending.
        ls_otab-descending = abap_true.
      ELSE.
        ls_otab-descending = abap_false.
      ENDIF.
      APPEND ls_otab TO lt_otab.
ENDLOOP.
    SORT lt_entityset_bfr_paging BY (lt_otab).
**Pagination
  LOOP AT lt_entityset_bfr_paging INTO ls_et_data FROM lv_skip to lv_top.
*  Provide the response entries according to the Top and Skip parameters that were provided at runtime
*    *  Only fields that were mapped will be delivered to the response table
    ls_gw_et_data-text = ls_et_data-text.
    ls_gw_et_data-name = ls_et_data-name.
    ls_gw_et_data-employeeid = ls_et_data-id.
    APPEND ls_gw_et_data TO et_entityset.
    CLEAR ls_gw_et_data.
  ENDLOOP.


Here we see a lot of unncessary loops getting executed to achieve the correct results. To avoid this loops we have another way …Yes you guessed it right to push pagination to backend side .This approach I will be covering in the next part

Good approach..

Here we pass the skip and top parameters to the backend FM .So we only selected (almost) the required records from the backend.

If we say skip 2 and top 4 , it means the records from 3 to 6 should be selected.

As first step we go ahead and change the backend API interface

FM_new_API.jpg

Now we change the source code to handle pagination




IF iv_skip IS NOT INITIAL
    AND iv_top IS NOT INITIAL.
    DATA(lv_up_to) = iv_skip + iv_top .
  ELSEIF iv_skip IS INITIAL
    AND iv_top IS NOT INITIAL.
    lv_up_to = iv_top.
  ENDIF.
  SELECT emp~id
  emp~name empt~text FROM zars_emp AS emp
  INNER JOIN zars_empt AS empt
    ON emp~id = empt~id
    INTO TABLE et_data
    UP TO lv_up_to ROWS
    WHERE empt~spras EQ sy-langu.
  if iv_skip is NOT INITIAL.
  DELETE et_data FROM 1 to iv_skip.
ENDIF

Here I am selecting records with “upto” clause and deleting all the records whose index is equal to or less than lv_skip.I agree that this is not the most efficient way of doing pagination . But we have to live with the same as $skip and $top are directly not supported in the open SQL select .

There are some workarounds with AMDP(Abap Managed Database Procedures) where they support LIMIT(top) and OFFSET (skip). But the issue it does not support dynamic values.Another method is to dynamic native sql using ADBC but that also comes with its own disadvantages..

Now we need to redefine the DPC_EXT GET_ENTITYSET method to integrate this call…


    lv_rfc_name = 'ZARS_FM_GET_EMP'.
    IF lv_destination IS INITIAL OR lv_destination EQ 'NONE'.
      TRY.
          CALL FUNCTION lv_rfc_name
            exPORTING
              iv_skip        = ls_paging-skip
              iv_top         = ls_paging-top
             IMPORTING
              et_data        = et_data
            EXCEPTIONS
              system_failure = 1000 message lv_exc_msg
              OTHERS         = 1002.
          lv_subrc = sy-subrc.
*in case of co-deployment the exception is raised and needs to be caught
        CATCH cx_root INTO lx_root.
          lv_subrc = 1001.
          lv_exc_msg = lx_root->if_message~get_text( ).
      ENDTRY.
   endif.
  
 LOOP AT et_data INTO ls_et_data.
*  Provide the response entries according to the Top and Skip parameters that were provided at runtime
*    *  Only fields that were mapped will be delivered to the response table
      ls_gw_et_data-text = ls_et_data-text.
      ls_gw_et_data-name = ls_et_data-name.
      ls_gw_et_data-employeeid = ls_et_data-id.
      APPEND ls_gw_et_data TO et_entityset.
      CLEAR ls_gw_et_data.
    ENDLOOP.

Its time to test the code . We head to GW client and run the below  URL

/sap/opu/odata/SAP/ZARS_PAGE_SRV/EmployeeSet?$skip=2&$top=3

/wp-content/uploads/2015/07/correct_results_741200.jpg

As you can see here we get the correct results 😉

Closing comments..

Here I have tried to explain an efficient(almost) way  of doing client side pagination. In my next blogs ,I will try to cover server side pagination and pagination with CDS views/AMDPs .Please let me know your comments for the blog

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply