Other than the obvious benefit of learning the best methods for dealing with large lists, there are a couple related nuggets covered in the white paper titled Working with Large Lists in Office SharePoint Server 2007 (discovered from the SharePoint Product Group blog) I wanted to blog about which I thought were interesting.
First is concerning the PortalSiteMapProvider. As mentioned in the paper, this is a not very well known class. I didn’t know about it, either. According to the paper, “it was originally created to help cache content for navigation”. The author, however, uses a method in the class called GetCachedListItemsByQuery to retrieve list items. To summarize why I like this class (and method), it basically provides logic to manage retrieving items and optimizing that retrieval by using the server cache. If the items are in the cache, it doesn’t hit the database. If the items are out of date, it refreshes. If the items aren’t there, it goes to the database to retrieve them and stores them in the cache for later. I’ve written lots of code to manage this for various objects in the past so I love seeing something built in for me to take advantage of.
The other nugget I pulled out of the paper isn’t specific to working with large lists but rather gives some nice insight into how list data is stored and retrieved. All data for all lists is stored in one table within SQL Server. I did not know this…which is not surprising since I haven’t yet spent time diving into the behind the scenes nuts and bolts. This is a nice and simple solution and makes it easy to understand where to find data, but it has its consequences. I don’t think I can explain it any better than the author, so here’s what he had to say:
“After there are approximately 5,000 items in a list, SQL Server will typically choose to lock the entire table for the duration of that change. In this event, all other reads and writes for all lists in all site collections are queued until the previous transaction is complete and the lock is released. This locking behavior occurs whether or not list items are recursively nested so that there are not more than 2,000 items in an individual container.”
Definitely something to keep in mind if you’re seeing some performance degradation.
It’s a nice white paper. Go read it.