SharePoint and large lists

Probably you already know or maybe you have a notion of that working with large lists in SharePoint cannot be fast. BUT – it depends.

I have read the Microsoft whitepaper on working with large lists some time ago. But I was not fully aware of what this means. Ok, using GetItemById is slow – but if I cache the result afterwards it should be ok, right?

That depends. With a few items in the list it works fine. With large lists it does not. I have made some measurements on my dev image with 1200 list items and three different implementations:

1. GetItemById method
  0.778081 s
  0.653073 s
2. CAML query
  0.039772 s
3.1 PortalSiteMapProvider 1. hit
  0.160483 s
  0.049452 s
  0.045379 s
3.2 PortalSiteMapProvider consequetive hit
  0.000576 s
  0.000333 s

The code for approach (1.) took 800 ms to execute on a state-of-the-art WFE server under little load with 2400 items in the list. This is way too long. So I implemented the standard PortalSiteMapProvider approach instead:

PortalWebSiteMapNode webNode =  
("/", SPContext.Current.Web) as PortalWebSiteMapNode;
SPQuery query = new SPQuery();
query.Query = SOME_QUERY;
SiteMapNodeCollection items = 
(webNode, SOME_LIST, query,
foreach (PortalListItemSiteMapNode item in items)
   if (item[SOME_FIELD] != null)
      // Do some stuff.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.