Skip to Content

Introduction

In the previous blog Pagination decrypted… we have seen how to implement client side pagination efficiently. Through this blog I will introduce server side pagination (which will be used mostly in offline scenarios) and considered better than client side pagination

Server says “I will decide”.Client says “I am delegating”

Both client and server will not be on the same page always (both have their own Ego).Lets see a fireside chat scenario 🙂


The server says:”I know how much records are there on the backend ,let me decide the packet size to be sent across the wire”

The client says : “Its because I am delegating the task to you(more like a manager), you are able to decide”

The client just delegates paging to the server, and the server provides a chunk of data, along with the total number of available items and  a link for asking more data.If you are an OData producer don’t forget to set Entity Set Page Size limit. If you are a Consumer, do look out for “next” link in the feed. Make use of this and loop accordingly through all the pages

Implementing server side pagination


Implementing server side pagination is pretty easy. The following steps have to be performed

  • Read the $skiptoken parameter from the client request
  • Decide on the packet size of the data to be sent back to the UI
  • If the number of result entries are greater than package  size , split the number of result entries
  • Set the last index of the result as the skiptoken for the client to fire the subsequent request


Enough of lecture …. Lets get our hands dirty and do something in the system.

We will try to implement server side paging for the test data in my previous blog


Data Modeling


I created a new entity type/entity set similar to Employee entity as in my previous blog Pagination decrypted… named EmployeeServer/EmployeeServerSet

/wp-content/uploads/2015/08/server_side_paging_1_764972.jpg



Backend API

I have two importing parameters for the backend API .. One for the skiptoken and the other one for the packet size which I have kept by default as 3.

/wp-content/uploads/2015/08/server_side_paging_3_765150.jpg

Here the exporting parameters EV_END_INDEX is for returning the total count as inline count and ET_DATA is for actual data

/wp-content/uploads/2015/08/server_side_paging4_765281.jpg

Now we need to handle this importing parameters in the source code .


**Find the no of rows to be selected from DB
  IF iv_skiptoken IS NOT INITIAL
    AND iv_packet_size IS NOT INITIAL.
    DATA(lv_up_to) = iv_skiptoken + iv_packet_size .
  ELSEIF iv_skiptoken IS INITIAL
    AND iv_packet_size IS NOT INITIAL.
    lv_up_to = iv_packet_size.
  ENDIF.
***Select data from table up to n rows
  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.
**Delete the initial skip entries
  if iv_skiptoken is NOT INITIAL.
  DELETE et_data FROM 1 to iv_skiptoken.
ENDIF.
**Send back index end for trigerring subsequent request
**Send back only if the total count in DB is less than Up to count
SELECT COUNT(*) from zars_emp INTO @data(lv_total_count).
ev_total_count =  lv_total_count.
if lv_up_to lt lv_total_count.
ev_end_index = lv_up_to.
ENDif.
**





From the above code snippet , you can see that its doing the following steps

  • 1 Find the number of rows to be selected from DB(Up to parameter)
  • 2 Select the data from DB
  • 3 Get the total count to be passed as inline count parameters(Will be useful if UI has to indicate the  user something like “3/10”..)
  • 4 Return the  index of the last selected row as the next ref link to fire the subsequent request

One minor improvement would be to set the total count (i.e 10 in this case) only if the consumer/client has requested for the same

So we add a new interface parameter to the API which indicates whether total inline count is requested from the consumer

/wp-content/uploads/2015/08/server_side_paging_impr_766020.jpg

Only query the count if it is requested


if iv_total_count_flag eq abap_true.
SELECT COUNT(*) from zars_emp INTO @data(lv_total_count).
ev_total_count =   lv_total_count.
endif.


Testing

We head to the gateway client for testing .

First we fire the request without any skiptoken and we expect three rows as we have set the packet size to 3

We fire the URL /sap/opu/odata/SAP/ZARS_PAGE_SRV/EmployeeServerSet?$inlinecount=allpages

/wp-content/uploads/2015/08/server_side_paging_gw_client_1_766021.jpg

Next we copy across the “Next Ref” link, append it to the existing URL  and fire the next request . This time we expect the next packet size and record numbers 4,5,6

/sap/opu/odata/SAP/ZARS_PAGE_SRV/EmployeeServerSet?$skiptoken=3%20

/wp-content/uploads/2015/08/server_side_paging_gw_client_2_766022.jpg

As expected it returns the next packet size with a link for the next call …

Conclusion

The choice of using Client  or Server side depends on the particular scenario. I personally prefer server side pagination as it gives more flexibility in the sense if the application scales , you control it from the server side and do not need to make any changes in the client side.But there is a catch to it, your backend SQL should be finetuned so that you select only the required records from DB.Thats the reason why I am huge advocate of LIMIT,OFFSET in open sql .Hope SAP lends some ears….

In my next blog on pagination, I will discuss how pagination can be applied on our new age artifacts like CDS views, AMDPs etc….

Stay tuned..

To report this post you need to login first.

1 Comment

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

  1. Ankit Maskara

    Hello Arshad,

    Good Blog. One potential problem which I see here is that the Search and Filter query options from UI won’t work properly if you directly apply the skip and top parameters on the Select Statement.

    BR.

    (0) 

Leave a Reply