This KBA explains which fields to use in the 'orderBy' clause that will actually help in removing the duplicates and not hamper the API performance.
Reproducing the Issue
In order to figure out the right fields that should be used in the 'orderBy' clause of any API query, you need to follow below steps-
1. Login to SFSF Instance.
2. Depending upon whether you are using SFAPI/Odata API, navigate to Admin Center->SFAPI Data Dictionary/Odata API Data Dictionary.
3. Now expand the entity for which you would want to use the 'orderBy' clause.
4. After expanding, observe the column 'Business Key' in the Entity definition and identify the fields that are marked as 'true' for this column.(as shown in below screenshot)
5. Now whichever field is marked as 'true' should be used in the 'orderBy' clause and that can vary from one entity to other. For example- some entity may have 1 field defined as 'Business Key' whereas some entity may have 2-3 fields defined as 'Business Key'.
6. But the number of fields should not be a problem, the only consideration is to use the field marked as 'Business Key' in the orderBy clause.
NOTE: It is a standard recommendation that if you are using an Odata Entity to fetch huge number of records from your SFSF Instance you MUST use the 'orderBy' clause i.e. Sorting in your API queries to avoid any duplicate records in the response.
Duplicate records, order by clause, business key fields , KBA , LOD-SF-INT-ODATA , OData API Framework , LOD-SF-INT , SF Integrations - EC Payroll, Boomi/ HCI, API , How To