Pagination decrypted(Part 2).. Serverside paging
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
I created a new entity type/entity set similar to Employee entity as in my previous blog Pagination decrypted… named EmployeeServer/EmployeeServerSet
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.
Here the exporting parameters EV_END_INDEX is for returning the total count as inline count and ET_DATA is for actual data
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
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.
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
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
As expected it returns the next packet size with a link for the next call …
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….