Sharepoint 5,000 item view threshold, but less than 1,000 items in view

Hi E-Matters,

What you always should keep in mind is the fact, that behind the view to limit down the items shown by the view is a SQL statement against the SQL server.
- Each lookup field creates a SQL join which is also limited inside a view, even inside the list itself.
- Every sorting field means, that all records have to be read. For SQL it is a major difference, if you first sort, then filter or first filter an then sort.
The second thought is why MS has set this limit(s) as it is connected with caching mechanism and therefore due to performance reasons.
And this is related to the SQL statement behind it. The trigger for the 5.000 item limit is not only triggered just by the number of result in SharePoint.

So, your construction looks like I would create it as well (in the first view), means all fields which are used for filtering, grouping, sorting etc. are indexed. Take care of any kind of lookup fields.

My start point would be to find out, if you can identify a defined fixed limit, means for example a number of returning records, when the message starts to comes up. I you can identify a clear limit, then the reason is more in the basic construction of the SQL statement behind the view. If you recognize that one views is working with 1000 results, but another with 900 not, then I would have a view on the single view definition for differences.  

I'm not quite sure (never tried to find out) if there is a possible difference in the way, how you have created the view. At least inside SQL you may observe, that the constructed SQL statement may differ, dependent from the steps you have done to create the view, although the result is the same at the end. So, saying this, it may be a try to see what happens, if you create a test view in the one or the other order. For example first to set the filter, save it, then to add a sorting or vice versa. Or just playing a bit around with sorting / grouping and without.

One option to work around is to raise the 5.000 item limit in the Web-Application. But this is not the best way due to performance reasons.
Another option is to think about archiving, means to move out older items into a separate list (you can do this automatically by a workflow) or just to delete older items (retention policy). As any list with 5.000 items has an performance impact, it is worth to think about a data lifecycle to keep the active list fast by limiting down the items and to move out older items into a more passive list (seldom used).
You list will raise anyway over the time, so whatever you do, you just move the problems into the future.  Your problem will come back earlier or later.

The 5k view limit is a litle tricky. You may only be returning 1k items in the view, but the filter and the index status of the columns is all important.

How it works is the very first filter applied in your view (there may be others after but this is the very first filter condition) must be against an indexed column, and that initial filter must be enough to get the total returned values under 5k in total.

This isn't well documented and it's commonly thought getting the total view returns under 5k is enough, it is not.

Please restructure your view along the above lines and you should get better results.

Bembi, thanks for your comments but they're not helpful. I'm aware of the need for limits, and how  archiving etc. should be considered. All of that is well documented. I've also tried re-arranging the order of the filters, as well as grouping differently, taking out totals etc. but the results are the same. I've also tried re-indexing.

According to Microsoft, a list can have 30 million rows, and return a maximum of 5,000 rows in a view. Further reading suggests that for a list with greater than 5,000 rows, the fields used to filter a view to below the 5,000 limit must be indexed.

My list fits those criteria but still the view limit is triggered.

If the field was indexed after the limit had been breached that can also result in it seeming Indexed, but under the hood it is not.

Best case here is to re-index that main filter field when the limit is relaxed. (Either you've set a time window in central admin during which the limit is relaxed or you use an admin account which has a higher limit, also specified in CA).

I've been through this issue ad nauseum with my own farms in the past. It is solvable.

Hi Jamie. Thanks for your comments. When you say to re-index when the limit is relaxed, what do you mean? I cannot remove or adjust the limit as it's O365 SharePoint, and I can't bring the list below the 5,000 limit in order to re-index. The current indexes were created at the outset, before any limits were likely to be breached.

Apologies I was indeed thinking of on-premises.

If the index is from the original setup before the size was reached that sounds good.

I suggest creating a view with only the first filter condition in place and see that really does reduce the returned results to below 5000.

What is the column type of that first filter?

Hi Jamie. Thanks for taking time to consider my question. I've tried creating the view with only the first condition, only the second condition, and with the conditions swapped, but I still get the error. My first field is a text field of 80 characters, using "begins with" as the filter and a single letter as the value. The second field is a date field, and I am using >[Today]-365 for the filter. And yes, I know leap years mean I won't always get a year but it's close enough for my purpose! Still, where I'd expect < 1k records in the view, I get the threshold limit error. Both fields are indexed, and removing sorting doesn't help either.

My nose tells me that a begins with and a text field are not the ideal first filter for a large list, but I found no actual documentation on that. That's just my SharePoint Spidersense twitching.

It's possible the index was created after list was already 5k, as discussed above this then looks indexed but isn't. If this is the case you might ultimately want to build a new list with index in place from the start and migrate the content to it.

Is it possible to create a field based on the data and filter on that? e.g. number based on first letter or something? Just speculating here, maybe a calculated column or a Flow could create the value.

Microsoft state the following as possible blockers also;

Do sort by only one column at a time

Don't sort by people, lookup or managed metadata columns

Don't group by

Don't add totals like count, sum, and average

Don't show more than 12 columns of the following types: people, lookup, and managed metadata

https://support.office.com/en-us/article/Manage-large-lists-and-libraries-in-SharePoint-b8588dae-9387-48c2-9248-c24122f07c59