API Pagination Limitations



Overview

At this point, there are no restrictions on pagination in Query API and Data API v4 - it is possible to request a page of any size. This may have significant performance implication in the event a very large page is requested.


Why Are API Pagination Limits Needed?

SAP Field Service Management is a multi-tenant SaaS solution, where multiple tenants share same database server. Heavy and long-running requests for one tenant often have an implication on other tenants, for example:

  • [Database] Long-running transactions in the database hold shared access lock on the tables preventing DDL migrations during new FSM version deployment/rollout
  • [Database] Select * From aBigTable is IO intensive operation which, if performed at scale, has significant impact on other queries performance
  • [Application] Application loads millions of objects (each DB row is a collection of 10-50 objects) into memory - in case such requests are sent for multiple customers it makes a heavy pressure on memory and garbage collection (can also lead to OutOfMemory under certain conditions)
  • [DevOps] Performance monitoring becomes harder as for the same API endpoint response time can differ significantly
  • Etc…

Affected APIs?


Timeline?

The API pagination restrictions will be enabled on 01.11.2020 in all production FSM clusters.


What Exactly Changes?

In order to optimise DB performance and avoid long-running transactions, the following changes will be implemented in the above-mentioned APIs:

  • Maximum page size will be limited to 1000 objects
  • Maximum number of objects retrievable via standard pagination will be 100,000 objects

If there are more than 100,000 results to be returned for the request, then the response will have the new property "truncated": true in the body:

{
  "data": [
    ...
  ],
  "pageSize": 100
  "currentPage": 1,
  "lastPage": 1000,
  "totalObjectCount": 100000,
  "truncated": true
}

Required Action

First, you need to identify all cases where your custom application, web container, integration, etc., make requests with pageSize returns larger than 1000 objects.

The subsequent actions differ by case, but in general the following consideration should be made:

  • If there are less than 1000 results expected to be returned for the API request, the easiest course of action would be to set pageSize to a value of 1000 or less.
  • If there are more than 1000 but less than 100,000 results expected to be returned, then iterate using standard pagination mechanism (page=1, page=2, page=3, etc.).
  • If there are more than 100,000 results expected to be returned from the API then iterate using keyset pagination (see below).

In case an integration with 3rd party system has to be adjusted, consider if it can be optimized first.


What if I Need to Retrieve more than 100000 Results from the API?

Implement keyset pagination over an ordered query based on object identifiers. In particular, when ordering by id property the client can use values in the current page to choose which items to retrieve in the next page.

For example, assume that the following request:

Request Value
Method POST
URL https://{cluster}.coresuite.com/api/data/query/v1?…&page=0&pageSize=5
Body SELECT a.id, a.subject FROM Activity a WHERE a.id>='00000000000000000000000000000000' ORDER BY a.id;

This request would return the following results:

a.id a.subject
0000049E09354AF6BEE7E741FCFEB68D Subject [97536]
00024E7754CC441FAD0EA95EE559E1B6 Subject [97342]
0002CD40C7CC4BDA9BAD8F29F05B75EA Subject [58113]
0003984B51274895B2998CE549DBA93B Subject [83831]
00040C36F17E4B0B88BC03EC56E0D2A1 Subject [17573]

Then in order to retrieve next page using keyset pagination approach, the following request should be sent to the API:

Request Value
Method POST
URL https://{cluster}.coresuite.com/api/data/query/v1?…&page=0&pageSize=5
Body SELECT a.id, a.subject FROM Activity a WHERE a.id>'00040C36F17E4B0B88BC03EC56E0D2A1' ORDER BY a.id;

Repeating the request in a loop would allow to read any amount of activities through the API.


How Can I Optimize Integration with FSM?

Typically, integration with 3rd party systems can be built using the following approach:

  • During initial data synchronization read all data from FSM and store it in the 3rd party system (provided that FSM is the master source of data).
    • record lastSyncTimestamp of this synchronization for later usage.
  • For all all subsequent calls, do in a loop:
    • use lastSyncTimestamp to read delta – read only the data which got changed since the moment of last synchronization, i.e.:
      • POST api/data/query/v1?… SELECT a.id, a.subject FROM Activity a WHERE a.lastChanged>=’2020-05-03T12:30:00Z’;
    • update lastSyncTimestamp accordingly.

This approach accelerates data synchronization between FSM and 3rd party systems and avoids a repetitive transfer of data which has not been changed since last synchronization run.


Contact Us

In case of questsions please drop an email to v.lezhebokov@sap.com.