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(); 
   2:   
   3:  List<int> itemIds = new List<int>();  
   4:   
   5:  foreach(SPListItem item in theList.Items)   
   6:  {  
   7:   itemIds.Add(item.ID);
   8:  } 
   9:   
  10:  foreach(int itemId in itemIds)  
  11:  { 
  12:   
  13:    SPListItem theItem =  theList.Items.GetItemById(itemId); //this will cause severe performance issues for large lists
  14:   
  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.


1 comment:

Pham Van Trung said...

Hi Paul
I think you do not update the SharePoint article from MSDN for a long time. Because MSDN have many articles that related to turning performance for SharePoint.

And your code is the BAD case, do not ever query like: list.Items.Count, list.Items.Add()....
It make SharePoint system Slow down.