Querying Very large list using Rest API - advice required

Question

We have a list, the items will be added everyday by scheduled job.

The number of items varies around 5000 to 12000 every day..

The list has columns like:

1. Country: Lookup
2. City: Lookup
3. StartDate: Date
4. EndDate: Date
5. Other columns

In our application we need to fetch the details from the list for today for the selected country from drop-down.

Is it feasible to use the below query to fetch only records for the selected country and for today's interval instead of recursive function to fetch items?

https://sites.test.com/sites/test/_api/web/lists/getbytitle('test')/items?$select=Title,City/Id,City/Title,ID,Country/Id,User/Id,User/EMail,User/Title,User/SipAddress,User/Department&$expand=Country,City,User&$filter=Country/Title eq 'UK' and StartDate le datetime'2019-03-12T00:00:00' and EndDate ge datetime'2019-03-12T00:00:00'&$orderby=Title&$top=4999

Answer 1

You can use this kind of query, but as you have more than 5k items in the list you need to have index on the columns you query otherwise the query will fail. As you are in SPonline remember to create necessary indices before you have more than 5k itmes in the list.

Word of caution. Although SP documentation is saying that list support millions of items it is not recommended to work with such large list (consider situation where you have index on 4 columns and suddenly customer wants now a filter against 5th column, in SPOnline you will need to create new list and migrate items to have index on that new column). If you still want to use it consider lifting the list to its own content database (in SP all lists data for multiple sites is in one table, if you have separate content DB for site col with that list you can mitigate that bottleneck this way)

Answer 2

Yes.

You can use specific filters like Today's date interval and countries.

There will be no issue in fetching items like this and it will work without fail.

Also, you are using $top which is additionally beneficial in this case.

However, if you want to get more than 5000 items then you need to make recursive calls and fetch the items by specifying 5000 in $top.

Reference: Rest to read more than 5000 items from SharePoint.

As you are using such large list, you should read how to manage large lists in SharePoint online.

Reference: Manage large lists and libraries in SharePoint.