I've got a list with ~10k items in it; I've indexed this on a number of columns and have recently started to see 500 errors when querying the list via REST ($.ajax) despite the queries previously returning 5k+ rows with no issues.

I appreciate questions in a similar vein are asked quite a lot but I've not seen anything that quite hits what I'm looking for.

If I run this:

https://mysite/_api/web/lists/getbytitle('myData')/Items?$select=*

Data is returned with no issues.

However if I run:

https://mysite/_api/web/lists/getbytitle('myData')/Items?$select=*&$filter=(DateReceived ge datetime'2018-04-01T00:00:00.000Z') and (DateReceived le datetime'2018-11-30T23:59:59.999Z')

I get a 500 error.

'DateReceived' is an indexed column and I've looked at the raw data, whilst there are over 5000 entries in the 'DateReceived' field; there's only ~200 unique entries.

There are some additional columns involved in the filtering and sort but all of these are indexed.

I've never come across this before, I have plenty of other lists well above the 5k limit that are all similarly indexed and no trouble there; part of me wants to delete the indexes and re-apply but wondering if there's something I'm missing here?

TL;DR

I have a large list with indexed columns but when I filter the list via REST, on an indexed column, I get 500 errors. I can however do a select * and return all data with no issues.

  • it is a long shot and may sound silly but try to add another clause to your query ID>0. I had situations it worked for me, don't ask me why I never managed to figure it out why it makes a difference. Another good thing would be to try to create view with same clauses and see how does it work. There might some bug in API that doesn't show in standard list view (and then you can open bug with MS) – Marek Sarad Dec 5 at 13:50
  • Thanks @MarekSarad, I have tried ID ne null/ID ge 0 in various combinations and no luck; I'm iteratively trying different combos on different fields which may be what I need to do until I get to the bottom of it. It's the fact that select=* works which is the most puzzling! I'll try your suggestion of creating a view however and see if that flags anything. Thanks. – BM4291 Dec 5 at 13:57
  • I was thinking about your issue, and I wonder isn't the problem that the Date Received isn't populated in some of the queries. Have you tried to add another clause to filter out the null values on the dateReceived and only after that apply the date filter? – Marek Sarad Dec 6 at 8:46
  • Unfortunately not having any impact; have tried quite a few combinations but every time I introduce DateReceived I have the same 500 error. I think I'm going to load everything (as that somehow works) and then filter the data array, it'll slow the process down from the end user but they'll get their report this way. – BM4291 Dec 6 at 11:39
  • Have you tried to recreate the indexes? Review the ULS at that time you will recreate the index. – Marek Sarad Dec 6 at 11:41

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.