Tuesday, September 22, 2009

SharePoint List Query performance gotcha

As i recently discovered, there is BIG difference between calling SPList.GetItemById() and SPList.Items.GetItemById(). The latter causes all items to be loaded into memory and then filtered. This can cause severe performance issues for lists with large number of items.
Below is the code that takes 5 hours to run on a dual core box when run against a list with 5500 items.

   1:  SPList theList = GetList(); 
   3:  List<int> itemIds = new List<int>();  
   5:  foreach(SPListItem item in theList.Items)   
   6:  {  
   7:   itemIds.Add(item.ID);
   8:  } 
  10:  foreach(int itemId in itemIds)  
  11:  { 
  13:    SPListItem theItem =  theList.Items.GetItemById(itemId); //this will cause severe performance issues for large lists
  15:  }

To fix this issue we need to change the code in line 13 to:

  13:  SPListItem theItem =  theList.GetItemById(itemId);

Now the above code will run in minutes.

I wish MSDN documentation on this topic was more clear. That would save me from putting in a couple of late nights at the office.