Large List Improvements in SharePoint 2010

Business Issue

With many customers we pushed them to move the majority of their content into SharePoint for all the added benefits that this offered.
Unfortunately this sometimes involved taking a huge shared drive and inserting it into one massive document library This would usually cause all sorts of issues, even more so when sometime power users would create views that would retrieve huge numbers of items from a document library or developers were working with large list items.

As SharePoint moved into the enterprise a better way was needed to managed potentially huge items within lists. SharePoint 2010 introduced new abilities to manage large lists to deal with these issues, lets check them out!

Business Value

1. Control: Now administrators can better control the use of resources that can be used for lists.
2. Options: There is now more options to deal with large datasets (Access Services, Visio Services, Excel Services)

Limitations

1. SharePoint Workspace 2010 will automatically retrieve data in batches of 100 documents at a time and then reassembles the data. It will ONLY however get 30,00o items per lists, if you have more than 30,000 items that from a list that you wish to take offline then you cannot take the whole list offline.

Throttles/Limits for Lists and Libraries

• Items: 50 million items per list or library limit
• File Size: 2BG file limit or item attachment limit
• List View Threshold: 5000 items default value. When a operation will return more than 5000 items throttling will occur
• Unique Permissions: 50,000 available per list or library
• List View Lookup Threshold: 8 the maximum number of join operations that can be performed
• List View Threshold Size: 20,000 items can be retrieved at one time when performed by an administrator or auditor with appropriate permissions.
• Allow Object Model Override: Default is Yes. This allows developers to perform operations and queries that can return more than the 20,000 result limit that is usually in place.
• Daily Time Window: Default is none. Specifies a time period where limits are ignored.

Why was the value 5000 chosen?

If a large amount of rows are to be returned SQL will sometime lock the entire table instead of using row locking. That’s a bad thing when multiple people are accessing the same table, especially since the operation will have to wait to execute before any other process can use the table. Resource Throttles and Limits make sure that an entire table is not locked and usually this is around the 5,000 item mark, hence the number being chosen.

How to Manage Large Lists and Libraries

Create Indexed Columns

You can index up to 20 columns in a list that will really improve performance on the list. However remember that each index will create extra resources so its not a good idea to index every single columns:

Create Views

Once you have created a indexed column you can then create a view that will limit the amount of items shown. The best approach is to set up a default view that returns less items than the throttle limit (5000). To do this you can create a view that filters on a specific value. If you use your indexed column that you created above you will get even better performance so remember to do this!

With views you have many options that are available to you but remember that the default view should be the one that will offer the most value to your end users, not one the will simply return as many items as possible without going over the limit

Some of the options available that would work well would be:

Specify Item Limits

• Display items in batches of the specified size: This will essentially create a paging mechanism that will retrieve items in batches rather than a whole hit.
• Limit the total number of items returned to the specified amount: This will create a hard limit for your items. Be careful since users will not know that the limit has been reached.

Use Microsoft Access 2010

You have two options here. The first option is to link use Access to simply interact with the data stored in a SharePoint list. Essentially you are using the Access interface to interact with the list but Access will cache some of the list data in memory on the local machine so you don’t have the same issues with only retrieving a certain amount of items. This linking option creates a two way connection to view and interact with the data stored within SharePoint.

The other option is to actually create an Access Web Database through Access Services. With Access Services you can create tables and lists, queries, forms, reports, macros and all the other good stuff that Access offers. The benefit is that you can work with far more data than the list threshold. Access Services retrieves data in batches of 2000 items at a time and the default limit is 50,000, around 10 times as much as the standard threshold.

Use Metadata Driven Navigation

Another technique would be to use Metadata Navigation to let users quickly and easily navigate through large result sets until they achieve a set of results that falls under the limit. Metadriven navigation allows the ability to navigate and filter results by metadata, allowing users to quickly and easily navigate through a collection of items and then reduce the result set to an acceptable limit.

Tips for Large Lists

1) Try to limit folders to 5000 items, one way to achieve this is by using the Content Organiser feature with Partitioning enabled to automatically create folders after a threshold has been reached.
2) When creating a view, make sure that the FIRST column in filter is indexed! Only the first index is used when defining a filter, even if other columns are indexed.

3) Try using Access Services for data instead of the SharePoint list functionality.

4) Use metadata driven navigation on lists or libraries to be able to quickly and easily drill down into results

Tags: ecm, lists, post-wide

Category: Planning, Requirements and Analysis