How to run ReST query to a list when the item count is more than the List View Threshold?

Question

I have a list that contains around 16000 items and will be growing. The List View Threshold is set to the default value of 5000 (SP 2010).

I have nothing to do with the SP list views and UI for this list and all I do with this list is query it using the ReST query with filters. The filtered query returns only a handful of items (at max 20).

The query was working fine until the count of item was less than 5000. But now it is returning Internal Server Error (HTTP 500) when I run the query.

Can someone help?

Answer 1

The first thing to do is to INDEX the columns that you'll use to filter your list. Then you always need to filter your list using one of the indexed columns.

Attention : let's say your list has 15,000 items. You have indexed a "Year" column and the "Month" column. If you filter your list with "Year = 2014 AND Month = 11", then it will only work if "Year = 2014" returns less than 5,000 items. So the first criteria in your WHERE clause must always return less than 5,000 items. This is tricky sometimes...

If you provide more info about your list (and your WHERE clause), I can try to give you more advice.

Answer 2

I also faced similar issue. My list threshold was 5000 items. I used following approach of getting data in batches:

1. Get id>0 and id<5000
2. Get id>4999 and id<10000

For example:

https://{{Sharepoint Url}}/_api/web/Lists/GetByTitle('Test')/items?$filter=(Id gt 0 and Id lt 5000) and {{Your Condition}}

Please let me know if you have any doubt.