Best Practices: SharePoint Object Model for Performance Tuning

Lots of situations where we will use APIs for retrieving information about Lists and List Items. In SharePoint, lists are the objects storing large amount of data. So we need to be little cautious while working with those APIs, because internally those APIs are calling some SQL queries to pull the data which has been stored the SharePoint Content DBs.

The performance issues may happen in some cases if numbers of lists are very high or in some cases total number of lists will be less but the items will be very large.  

First we can take a look at different approaches of getting SPList instance and we can choose the best method to increase the performance.  We have more than one method or property which will return the same result. For Eg: SPList.Item.Count & SPList.ItemCount will return the number of items, so here we need to decide which one need to opt in our code implementation to enhance the performance.

Scenario 1 : Retrieve SPList instance

SPWeb.Lists (“name”) – Not Good L

using (SPSite site = new SPSite(strSite))             {                 using (SPWeb web = site.OpenWeb())                 {                     SPList oList = web.Lists ["MyList"]                 }              }

In this case, it loads the metadata* of the all lists in that specificSPWeb object. Then it does SPList.Title comparison with metadata of all the lists returned and then it returns the matching list from the SPWeb.Lists collection.

SPWeb.GetList (string strUrl) – Good J

using (SPSite site = new SPSite(strSite))       {        using (SPWeb web = site.OpenWeb())         {           SPList oList = web.GetList("http://Site/list/AllItem.aspx")         }       }

In this case, first retrieves the list GUID from the url (database hit), then it loads the metadata* for that specific list.

metadata * = list of all information of List like its schema, fields info, content type info, column and items count.

Consider a scenario of a SharePoint site which contains 1000 lists.

If we use SPWeb.GetList(), it will load the SPList by finding out the exact GUID of that SPList from the SharePoint content DB and loads the metadata.

But if that is the scenario with SPWeb.Lists[“MyList”] then, SPWeb.Lists will load the metadata of  all the 1000 lists in memory and then it does SPList.Title  ( here it is “MyList”) comparison with metadata of all the lists returned and then it returns the matching list from the SPWeb.Lists collection.

If you debug the code in winDbg then you can find out the GC Heap size and then you can realize how badly it is affecting the performance of your application, sometimes for each SPList it will take some MB's.

So now we can consider this matter while writing code and use SPWeb.GetList() instead of using SPWeb.Lists[“MyList”].

Scenario 2 : Retrieve SPListItem

SPList.Items[int idx] – Not Good L

using (SPSite site = new SPSite(strSite))             {                 using (SPWeb web = site.OpenWeb())                 {                   SPList oList = web.GetList("http://Site/list/AllItem.aspx");                     for(int idx =0; idx< oList.ItemCount; idx ++)                     {                         string strLstItemName = oList.Items[idx].Name;                     }                 }             }

In this case, for each iteration oList.Item[idx] will load aSPListItemCollection. Eg: consider a list has 1000 list items. So whenever this code executes, for each iteration it will create a separate SPListItemCollection and it will create a huge memory consumption in the GC Heap by creating 1000 SPListItemCollectioninstances

SPListItemCollection[int idx] -  Good J

using (SPSite site = new SPSite(strSite))             {                 using (SPWeb web = site.OpenWeb())                 {                    SPList oList = web.GetList("http://Site/list/AllItem.aspx");                     SPListItemCollection oListItems = oList.Items;                     for(int idx =0; idx< oList.ItemCount; idx ++)                     {                         string strLstItemName = oListItems[idx].Name;                     }                 }             }

In this case, we can see the the only code change between this one and the not good one is, here we are first taking all the items from the list and populating it in a SPListItemCollection. And then we are iterating only that SPListeItemCollection and finding out the specific list item. Here the advantage is that, in the memory this code will load only one SPListItemCollection.

Scenario 3 : Retrieve SPListItem in Event Handlers

SPListItem – Not Good L

public override void ItemAdded(SPItemEventProperties properties)  {   using (SPSite oSite = new SPSite(properties.WebUrl))    {     using (SPWeb oWeb = oSite.OpenWeb())     {      SPList oList = oWeb.Lists[properties.ListId];      SPListItem oListItem = oList.GetItemByUniqueId(properties.ListItemId);     }    }  }

In this case, we are unnecessarily giving extra load to the memory by adding so many memory consuming APIs.   For each iteration, oList.Item[idx] will load a SPListItemCollection. Please see the Good method below.

SPListItem – Good J

public override void ItemAdded(SPItemEventProperties properties)  {   SPListItem oItem = properties.ListItem;  }

In this case, we have reduced lots of code and it will return the current ListItem by using this single line of code. Avoid creation of SPWeb & SPSite instances, because in an event handler those are directly accessble through the SPItemEventProperties.

Scenario 4 : Retrieve SPListItem Count

SPList.Item.Count – Not Good L

using (SPSite site = new SPSite(strSite))       {        using (SPWeb web = site.OpenWeb())         {           SPList oList = web.GetList("http://Site/list/AllItem.aspx");           int iCount = oList.Items.Count;         }       }

In this case, oList.Items.Count, first it will load all the SPListItems in the memory and then it will find out the total count. For eg: Consider a list with 1000 list items. Then in this scenario the above code will load all the 1000 SPListItems and then return the total count, which will really create some performance hit.

SPList.Item.ItemCount – Good J

using (SPSite site = new SPSite(strSite))       {        using (SPWeb web = site.OpenWeb())         {           SPList oList = web.GetList("http://Site/list/AllItem.aspx");           int iCount = oList.ItemsCount;         }       }

In this case, ItemCount is a part of metadata of the SPList object and this will get generated whenver we create a SPList instance. So there is no any overburden to the list to find out its total number of list items.

Scenario 5 : A list of recommended  properties and methods

Not Good (replace this by the Good one)	Good  J
SPList.Items.Count	SPList.ItemsCount
SPList.Items[Guid]	SPList.GetItemByUniqueId(Guid)
SPList.Items[Int32]	SPList.GetItemById(Int32)
SPList.Items.GetItemById(Int32)	SPList.GetItemById(Int32)

Scenario 5 : Specify the RowLimit Property while using SPQuery Object

SPQuery.RowLimit  – Good J

SPQuery oQuery = new SPQuery(); oQuery.RowLimit = 2000;

Performing an SPQuery without setting RowLimit will perform purely and will be fail on large lists. Thus it will be always recommend to specify the RowLimit between 1 and 2000. Because if we didn’t mention it, in SQL server it ill return the resullt by using “select top x from table”, here the x will be a very large number. So it would give a very good performance if we limit the row by explicilty setting the RowLimit. Also, the query must use an indexed field or it will cause a complete table scan and WSS will block it on a large list.