Friday, October 17, 2008

How Well Do SharePoint Lists Scale?

List Scalability

List scalability is a hot topic in the WSS/MOSS arena, and for good reason. Pretty much everything in SharePoint is stored in a list. Knowing if you're likely to hit a wall with scalability is a proactive step that can save you a tonne of time (and help you decide if a List is an appropriate container in the first place).

Rules of Thumb

When it comes to putting a tonne of data into a SharePoint list I would consider the following guidelines:

  1. No more than 2000 items per view/container. That could be 1900 items and 100 folders but either way, no more than 2000 per view. A folder is an example of a container, so is the "root" level of a list.
  2. No more than 5 million items per list. This is the max, it's only really do-able by nesting many folders together which in turn hold more items.
  3. No more than 2000 lists per sub site (SPWeb).

Methods of Access

It's also worth noting that depending on how you access data from a list you can get dramatically different performance (especially for large data sets). The following white paper from Microsoft (which was described as a must read by Joel Oleson) compares the performance of getting data out of lists with the following techniques:

  • SPList with For/Each
  • SPList with SPQuery
  • SPList with DataTable
  • List Web service
  • Search
  • PortalSiteMapProvider (yes you can query a list with a portal site map provider...who would have known).

For those simply to busy/lazy to give the article a read, I'll try say that in general getting data from the PortalSiteMapProvider is worth looking into (since it's cached) if the data isn't going to be changing that often.

Search and SPList with SPQuery were also strong performers when it came to getting data from lists with 100k+ items.

What's more important is that SPList with For/Each and SPList with DataTable (both below) just don't scale well for large lists.

//THIS CODE DOES NOT SCALE
SPSite site = new SPSite("http://someurl.com");
SPWeb web = site.OpenWeb();
SPList list = web.Lists["ListName"];
SPListItemCollection items = list.Items;

foreach (SPListItem in items)
{
//do something clever
}
//THIS CODE DOES NOT SCALE
SPSite site = new SPSite("http://someurl.com");
SPWeb web = site.OpenWeb();
SPList list = web.Lists["ListName"];
DataTable items = list.Items.GetDataTable();

//Doesn't scale even if you use a data view
foreach (DataRow in items)
{
//do something clever
}

You can probably see what the above codes have in common, they both get EVERYTHING out of the list which is a big no, no. What scales a lot better is retrieving a subset of what you're after from the view with an SPQuery, or other like minded code.

//THIS CODE SCALES
SPSite site = new SPSite("http://someurl.com");
SPWeb web = site.OpenWeb();
SPList list = web.Lists["ListName"];
SPQuery query = new SPQuery();
query.Query =
@"<Where><Eq><FieldRef Name='Name'/><Value Type='Text'>
Joe</Value></Eq></Where>";
query.RowLimit = 100;
SPListItemCollection items = list.GetItems(query);

foreach (SPListItem in items)
{
//do something clever
}

Know The Limits

SharePoint will a LOT of work for you, but there's a balance that needs to be observed. Knowing when the platform is at its limits is key to differentiating what the platform can and cannot do for a project.

Hopefully the above code will help you get a little more out of your lists. If you're more interested in general scalability (how many lists to a site, how many sites to a site collection etc...) there's a great TechNet article here on the topic.

Best,
Tyler

1 comment:

Andreas Grabner said...

Hi. Check out my blog series about the same topic @ http://blog.dynatrace.com/category/net/sharepoint-net/
Here I actually show what is going on the database layer which explains why some of the approaches do not scale