With this post, I want to discuss practical design rules you need to consider when dealing with large lists in SharePoint 2010.  We had to learn these concepts the hard way when we were engaged by a large company to build a replacement for 700 sales guys using Salesforce.com with SharePoint during the beta of SharePoint 2010.  We knew we would have to build the application to support large lists and run in the cloud (BPOS), so around October of 2009, we were whistling and generally happy, thinking that we could now support really large lists and build some really amazing applications, with this being the first of many to come.

Well, to cut to the chase, we started bumping into some limitations on large lists with hundreds of thousands of items that I want to share with Bamboo Nation so that you can avoid some of the same mistakes and assumptions that we struggled with.  Let's start with the first hurdle we ran into – throttling.  Throttling allows you to set limits on a lot of things in SharePoint 2010, including how many list items a user can see in a single query or view.  For a full description of throttling, see this TechNet post for a great discussion.  This brings us to Design Consideration Number One when dealing with large lists in SharePoint 2010.

Large List Design Consideration Number One – Throttling

It is very important to get requirements from your users in order to determine how many items they reasonably expect to retrieve in a view or search result when working with large lists in SharePoint 2010, and then set an appropriate throttling limit.  It's equally important to know which properties should be indexed to prevent full table scans which can cause you to bump into throttling limits

Note that you can set throttling values for two different types of users out-of-the-box — say, a regular user versus an admin or auditor.  You can also specify a time when large queries can be executed, and another parameter lets you set how many properties can be used for lookups in a list view. Throttling is an extremely important facet that you must now plan for in any SharePoint 2010 deployment.

Getting back to our story, remember that we were designing this application to run in the cloud so that we could replace Salesforce with a similar cloud-based offering.  This introduced the next important concept we had to overcome.

Large List Consideration Number Two – The Cloud and Throttling Restrictions

Throttling in a multi-tenant environment (i.e., "the cloud") may be imposed on you by the provider of the SharePoint cloud service. It is imperative that you understand any throttling restrictions which may be imposed upon you by your SharePoint cloud provider BEFORE you sign up for the service, else you will risk disappointing users. 

You may have requirements for very large search result sets (IE building reports) that may not be supported by your multi-tenant cloud provider.  Make sure you check into throttling thresholds offered by your cloud provider, and that those thresholds are sufficient to meet your needs before you move to the new environment.

OK, we came to appreciate and understand throttling, and designed the application that would perform well in a throttled SharePoint 2010 ecosystem whether on premises or in the cloud.  We were happy again and we started loading hundreds of thousands of items to lists (this client has 700 sales people so they have a lot of leads, customers, opportunities, etc).  To make matters worse, each list item requires permission on it so that only certain people can view or edit the list item. As we began to really load up the system, performance started to DIE and we started wondering what was going on.  I thought you could have millions of items in a SharePoint 2010 list? 

For technical reasons beyond the scope of this blog, it turns out that there is a practical limit of a few thousand items with unique permissions in a SharePoint 2010 list.  Trust me – this is a hard limit. You can't really support more than several thousand items with unique permissions on them in a single list. So with this realization, what were we going to do?  Hmm, thinking, thinking…

We realized that we had lumpy permission sets that applied to many list items within the list.  Hmmm, thinking, thinking…  Hmm, what if we used folders?  Hmm, that would mean we could shove hundreds of thousands of list items into several thousand folders and inherit permissions from the folders.  Hmm…

Folders saved us.  We were able to build a workflow that "automagically" routed items to the appropriate folder based upon values in the list item with SharePoint workflows.  We were lucky since we only needed a few thousand or so unique folders, and the system worked beautifully.  It also solved the problem of security trimming in search results.  Voila!  This leads me to the next consideration.

Large List Consideration Number Three

If you need to have unique permissions on items in a large list, you should group items with the same permissions into a folder and inherit permissions from the folder.  You should generally not have more than 5,000 items in a folder.  So if you have 2,500 folders, each with 5,000 items, you should be able to successfully manage 12,500,000 items as long as each item in each unique folder shares the same permissions.

There is a lot more to talk about around large lists, but this is a good start. Between this and my next post, check out the new "Content Organizer" feature.  It is probably a great fit for dealing with large lists as well.

My next blog will talk more about this project and how we successfully replaced a company with 700 sales guys using Salesforce with SharePoint 2010. It will center on InfoPath and some interesting ideas we are putting into the application using the new social computing capabilities in SharePoint 201o.  Until next time… 

Dave Chennault can be reached directly at DaveC@SkylteSystems.com.

Read all posts by Dave Chennault:

• SharePoint – The Next Killer iPad App
• BPOS Wave 14… How it Will Change Everything in the Next 12 Months
• 3 Design Considerations for Large Lists in SharePoint 2010