What’s a large list? Anything over 5,000 items.
Why is this such a low limit?
Great question, and Microsoft does a good job explaining it in their help documents:
To minimize database contention SQL Server, the back-end database for SharePoint, often uses row-level locking as a strategy to ensure accurate updates without adversely impacting other users who are accessing other rows. However, if a read or write database operation, such as a query, causes more than 5,000 rows to be locked at once, then it’s more efficient for SQL Server to temporarily lock the entire table until the database operation is completed.
When the whole table is locked, it prevents other users from accessing the table. If this happens too often, then users will experience a degradation of system performance. Therefore, thresholds and limits are essential to help minimize the impact of resource-intensive database operations and balance the needs of all users.
Do you think you’re going to exceed 5,000 items?
There’s a few things you can do before you exceed 5k, which will make life a lot easier.
Create indexes! Indexes are pockets of focused data that render results much faster (very laymen terms here). Searching for ‘bob’ against a name field is faster if that field is indexed.
Office 365 boasts that it will automatically create indexes, sadly I’ve come across some large lists without indexes, nothing created. Reached out to Office 365 support to create some, I’ll let you know if they actually do (requested a few weeks ago!).
Look for fields that you’ll be using to create views and index those. Think broadly like categories, dates, departments, etc. The ID field IS indexed automatically out of the box. Otherwise, create some more indexes.
I’m over 5,000 items, now what?
In the past, working with SharePoint installed on-premises, you could override the limit and go higher than 5k and keep on truckin. If you wanted to stay within Microsoft’s recommendations, there are opportunities to open up maintenance windows to allow bulk operations on lists larger than 5k.
In Office 365, we don’t have that flexibility. Microsoft actually has a list limit of 30,000,000 (30 million) items. 30M!?! That’s far over their definition of a large list. But the list view limit is 5k (.01%). Weird but I guess I understand why.
So what do we do? We have a few options, depending on your needs.
Users need to find records to update or reference
Create smaller collections of data.
If you’re fortunate enough to get your fields indexed, you’ll still need to keep your views under 5k items. You can have hundreds of thousands of items in your list, but you can’t serve up more than 5k items at a time to your users.
Using your indexed columns, create your filtered views. You’ll notice that your columns will be labeled as (Indexed):
PLEASE NOTE that just because you filter on an indexed field does NOT mean you’ll be under 5k items. Also, you can filter on a non-indexed field IF you include a filter for an indexed field, which by itself would return less than 5k items. For example, consider a list with 10k items:
- Filter State (Indexed) by ‘Connecticut’, and you get back 6k items. Filtering by City (not-indexed) ‘Hartford’ might return 400 items, but because the indexed filter returns over 5k, you can’t perform additional non-indexed filters against the data.
- Filter State (Indexed) by ‘Massachusetts’, and you get 4,900 items, then you can filter by City ‘Boston’ and get your subset of 400 items.
The magic of the ID. The ID column is automatically indexed, out of the box, even though it doesn’t show it in filtering. Sometimes if you need to find specific data you can create a view with a range of IDs and get your items. You can also create a view that shows all data, sans filter, but sorted by ID, that will work because that column is indexed.
This is great, but if you don’t have indexes…
Search is awesome.
Search can handle your list, and a gazillion of other records of data. Once the item is indexed, you can search for it pretty easily.
For specific business cases, like a call center for instance, you can create a ‘search application’ in SharePoint to allow call agents to find exactly what they’re looking for, by searching for specific data points like casenumber:1234, or callername:Bob. Some of this functionality would usually be done at the list level using column filters and such, but due to the mass amount of data, search is the answer.
I’ll cover more on creating a search application in my next post, I promise. For now, just try searching for your content, should come back for you.
Users need to report on the data
This is the biggest pain in the butt by far. Managers and teams want to see some sort of reporting, like how many calls in the last week, how many from a certain state, etc. Normally we could fudge this with the out of the box list views, but alas, there’s too much data. Instead we can use additional tools to get this data and report on it.
Microsoft Access is an option, though I don’t like it. If you’re looking for a fast solution, connect your local Access database to SharePoint and suck down that list. You can create reports and even export easily to Excel. This option does require your users to have Access installed to view the data (<– the reason I don’t like it)
Power BI is by far the better option (I know, it all depends on requirements). You can point PowerBI to your huge SharePoint list, it will take time to import it all (I imported 300,000 items in about 45 minutes) but once it’s there, you’re golden. You can even setup a nightly refresh so your data is fresh every day when you come in. Awesome sauce right? See below
Sharing PowerBI reports is easy through the standard Office 365 Share experience. According to Microsoft, PowerBI Pro is only available for E5 licenses, but I’m on an E3 and I can do a ton. E5 does offer more functionality but just pulling in data, creating reports and sharing, is doable on E3.
That’s a wrap! Let me know if I’m missing something, I’d love to hear your war stories. ‘Til next time, Happy SharePointing!
