Remove the list view threshold (5000 by default)
This limit has always been a bit laughable, and is even more so as we develop more client side applications. In SharePoint 2007 we didn't have this limit and were allowed to make our own mistakes. Now that hardware is so much more powerful, we need this limit removed so that we can build enterprise-class applications.
We are continuing to make our large list experiences better, please keep the feedback coming.
Spring 2018 update:
- We now support being able to manually add indexes to lists of any size (increased from lists up to 20,000 items previously).
- Starting with the February release of the Office 365 Excel client, you will be able to export your full list instead of getting cut off part of the way through.
What we are working on now:
- Predictive indexing will start to work for lists larger than 20,000 items so your views will automatically cause the right indexes to be added to your lists.
In our backlog:
- Being able to index/sort/filter by lookup column types (like person, lookup or managed metadata columns) without being throttled.
- Making sure that our REST APIs support querying in ways that will guarantee that the call will not be throttled.
For a general update on large list capabiltiies, the video on myignite.microsoft.com/videos/53861 (focusing on large lists at 42 minutes and 25 seconds) describes some of the changes that we delivered back in the second half of 2017:
- Modern UI now has a lot of support for adding indexes to large lists and libraries on the fly, reducing the number of throttling errors experienced by our users, and some new UI for browsing through items in large lists with our paging model
- SharePoint runs predictive indexing jobs to automatically add indexes as lists get larger based on your view definitions, and updates these indexes when you add/update your views
Looking forward to receiving more of your feedback.
Aymeric de Montpellier commented
At least the threshold should not be activated based on the first argument of the WHERE clause....
For example: I got a list with the columns `Data`, `Week` and `Year`. I have 1,000 items recorded each week.
I need all data from Week 1 to Week 3 (for reporting purpose). If I do my WHERE clause with « `Year` = 2015 AND Week >= 1 AND Week <= 3 » then I'll get an error, even if the final result will return less than 5,000 items. And I can't do « Week >= 1 AND Week <= 3 AND Year = 2015 » because it will return an error as well. And again if I do « Week <= 3 AND Week >= 1 AND Year = 2015 » because it will look at Week 1, 2 and 3 for all the years recorded.
Moreover, when we find that a list is too big (> 5,000 items), we cannot index the columns because it throws the threshold error.... We can only do it during the special time set up by IT. In my company, the servers are in the USA and I'm in France, so I have to index my columns at 1 am because of the threshold, during the special time!
Also the Lookup columns limitation as well as the Workflow limitation are very boring.
I'm pretty sure you can create something safe for the database, and not painful for users.
In the 90's we put everything in folders and subfolders. It sucked. Now with SharePoint, we can tag files with metadata and create views to organize our files dynamically. If I have to break up everything into 5,000-item libraries, I'm basically going back to folders again -- the dynamism is entirely lost. YES I KNOW I can create special queries or buy web parts to make it look like everything is stuck back together. But what about my users? They don't understand that stuff, they want it to just work. How do I begin to explain a 5k limit to them, and really, why should I have to? It shouldn't be essential to their day to day.
> Other times we simply need more than 5000 items. Period.
Echoing Marc's comments. I just built a Document Center to house my organization's HR files. It works great. But we're already at 25,000 items, and we'll likely be at 100,000 items in a few years. I'm not going to even try to predict how to divide 100,000 items into 5k chunks in a future-proof way. And how would I sell project that to my directors? "Sir, I need probably 50+ libraries, requiring extensive build and training, instead of just the one." "Why?" "Well, you need to know the limitations of Windows Server to understand." I built workarounds for the 5,000-item limit for now, but honestly, if it weren't for SP's retention policies, we wouldn't have considered SP a future-proof storage solution *at all* thanks to this stupid limit.
The answer is NOT more education. The answer is raising the limit to 1 Million Files. That's your goal. And honestly, the question isn't whether you'll do it, it's which competitor will beat you to it.
I think a lot of the problem is education. Maybe Microsoft could produce some training materials or another edX SharePoint course specific to overcoming the threshold limits and explaining them. I attended this one back in August, which was very good: https://www.edx.org/course/sharepoint-basics-it-professionals-microsoft-cld202x.
Today one of my users encountered the 5,000 item limit. After speaking with him, I found out they have no need to retain items in the list more than 90 days old. The simple solution was to delete everything that was more than 90 days old and then set a retention policy to dump things in the recycle bin after 90 days. Now they'll never run into the problem again (unless their normal daily volume increases substantially).
Marc D Anderson commented
Many times the only reason we need 5000+ items is to do some counts or sums. If those capabilities were there, it would cover a lot of ground. Other times we simply need more than 5000 items. Period.
This has always felt like an artificial limitation. In SharePoint 2007, there's no limit and I've built applications that retrieve 20000, 30000+ items using the SOAP services because I truly need to. This is the second decade of the 21st century. If the limit was 100,000 it might make more sense. 5000 is simply way too low. This is especially true since you guys tell us that lists con contain millions of items. If we stuff millions of items into a list and can't retrieve them, then it really defeats the purpose.
Martinus Hamers commented
Big Throttling issues here with Project Online! Need solution!
Maybe there should be more focus on a solid setup instead of launching new features and apps every day!
The recommended list option is ONLY 5000, the structure has been indicated to hold up to 30 Million items. but I had a list over the 5000 and experienced issue with IE and MS will not resolve. We cant be the only company that would like lists with over 5000 items. The idea would be a special list page - the list page could handle over 5000 item, but the requirement is the information be hosted as an external list.
Joao Livio commented
Mark. SharePoint Lists are not a joke since you follow the good methods, like indexing, tagging, folders etc.. If you have a publishing site for publishing pages you want to use a list in order to use a specific page layout.
Simple answer is do not use lists and move to a database for all your data requirements. SharePoint lists are nothing short of a joke.
Eric Fultyn commented
List throttling the way it is implemented, while good intentioned, seems to cause more problems for end users AND administrators than it solves. If a list is over the threshold limit, it won't let you do anything without upping the threshold, rendering the list useless. When you up the limit, you defeat the purpose of having that limit in the first place. I don't know if getting rid of a limit is necessarily the answer, but it should behave more like the maximum page amount that one can request at one time, rather than the maximum total items you can have before you are blocked from doing anything. As a farm administrator and a developer, I have seen absolutely no benefit to the current implementation. My vote is to fix it or forget it.
This request was already voiced here: http://sharepoint.uservoice.com/forums/282887-customer-feedback-for-sharepoint-server/suggestions/7077672-increase-remove-throttling
Agree, we had a lot more issue to convience the client when we built Global Record Management system for them, though we used managed metadata and term base navigation to overcome the challenge
Martial Dautremont commented
We need MS to provide a solution to overcome this limitation.
Yes, SP is no DBMS but business cases often deal with more than 5000 results.
Combined with the 8 lookups limit, it is a big burden !!!
Shogo Akimoto commented
Yes, this is the lock escalation limit on the SQL Server. But expect to impliment other ways (like paging) for removing this limit even if it will be performance down.
Trevor Seward commented
This is a SQL Server limitation, where requesting >5000 items introduces a table-level lock, versus a row-level lock for <5000 items.
Kushal Arora commented
Throttling is a big pain for many customers. If you somehow increase the throttling (no. of items in a list and no. of lookup columns) limit without impacting the performance then it will be beneficial for a larger audience.