共用方式為

Best Practices: SharePoint Object Model for Performance Tuning

  • 發行項

I am writing this post because of being inspired by couple of performance related cases that I had worked with my customers also one of triages that I had attended and it was taken by our Escalation Engineer Nishand. He did a very good job by finding out the inside edge of SharePoint APIs and comes up with very good alternate workarounds to enhance the performance of SharePoint code execution.

While working with SharePoint object model, most of developers will use SPWeb, SPSite, SPList objects intensively.

I had worked with some customers who reported performance issues in their production environment after their project deployment. Their code works well in most of the scenarios but whenever the data get increases then there might be potential performance hits because of not handling the APIs properly.

In this post I will give information about some common methods that we are using in most of the scenarios and very important points that we need to remember in perspective of application’s performance. We do have two cool MSDN articles gives information about the best practices with SharePoint object model and I will recommend you all to go through those articles as well. Also we do have an excellent blog by Roget Lamb by giving the detailed information of Dispose Patterns with examples.

Best Practices: Common Coding Issues When Using the SharePoint Object Model

Best Practices: Using Disposable Windows SharePoint Services Objects

Roger Lamb’s cool post about SharePoint 2007 and WSS 3.0 Dispose Patterns by Example

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 specific SPWeb 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("https://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 useSPWeb.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("https://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 a SPListItemCollection. 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 SPListItemCollection instances

SPListItemCollection[int idx] - Good J

  using (SPSite site = new SPSite(strSite))

            {

                using (SPWeb web = site.OpenWeb())

                {

                   SPList oList = web.GetList("https://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("https://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("https://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.

 

I hope all these information will help the developers while writing the code in their custom SharePoint applications. Since in SharePoint most of the data are storing in the lists, the maintenance of those tables in DB as well through code (by querying through SharePoint APIs) will be always be a best practice. Also everybody can consider these points while reviewing the code.

Using Visual Studio TFS 2008, we can do the performance testing of methods by profiling the methods and we can do a load runner test by simulating the requests from users and the time. It is really a great facility in VS 2008.

I would recommend all readers to visit my this post to get to know about a new tool (SPDisposeCheck) from Microsoft to find out the undisposed objects in your custom assembly and a new MSDN article (detailed one) about the disposing of objects in SharePoint.

Happy coding J

Comments

  • Anonymous
    October 26, 2008
    PingBack from http://stevepietrek.com/2008/10/26/links-10262008/

  • Anonymous
    November 04, 2008
    Üks Microsofti Sharepointi insidentidega tegelev inimene on kirjutanud väga kasulik parimate praktikate

  • Anonymous
    December 02, 2008
    Hi, thanks for this post. Very helpful. So would I be correct in saying that if I was to add a new list the following code would not be recommended: Guid listId = web.Lists.Add(listName, listDesc, SPListTemplateType.GenericList); SPList list = web.Lists[listId]; list.ContentTypesEnabled = true; ... list.Update(); ... Rather I should use: SPListCollection listCollection = web.Lists; Guid listId = listCollection.Add(listName, listDesc, SPListTemplateType.GenericList); SPList list = web.GetList(string.Format("{0}/Lists/{1}/AllItems.aspx", web.Url, listName)); list.ContentTypesEnabled = true; ... list.Update(); SPContentTypeCollection contentTypeCollection = list.ContentTypes; contentTypeCollection.Add(myContentType); BTW: In scenario 2, marked "Good", you use "web.Lists["MyList"];" when I think you should be using "web.GetList("http://Site/list/AllItem.aspx");"   :) Cheers, Martin

  • Anonymous
    December 03, 2008
    You are correct, if you use GetList() method then it will increase the performance. So, I too proved that it is common developer's mistake that we use web.Lists[""] :) (joke) Thank you very much for noticing this Martin. I have modified the scenario 2 and now the "Good" looks really "Good" :) Thanks, Sowmyan

  • Anonymous
    December 15, 2008
    Linki, które posłużyły mi przy tworzeniu prezentacji, z których czerpałem wiedzę, nakładałem ją na to

  • Anonymous
    January 13, 2009
    Great articel. I just worked on a series of blog entries about the same topic. Have a look at http://blog.dynatrace.com/category/net/sharepoint-net/. Here you will also see what is actually executed against the database in the individual use cases that you showed. Regards Andi

  • Anonymous
    January 13, 2009
    Thanks Andi. I just read through it.Great !

  • Anonymous
    January 14, 2009
    Thank you for the great research. Very helpful :-) René

  • Anonymous
    January 15, 2009
    Looks great. I printed it out for my permanent file. Thanks! -Tom

  • Anonymous
    January 15, 2009
    Thanks for this . Great . Its very helpful

  • Anonymous
    January 31, 2009
    Microsoft has released 3 cool things for SharePoint develoepers recently. 1. SPDisposeCheck v1.3.1 2.

  • Anonymous
    February 02, 2009
    We are going to adopt these practices. Great post!

  • Anonymous
    February 02, 2009
    Hi Mathew, Please check my latest post: http://blogs.msdn.com/sowmyancs/archive/2009/02/01/three-cool-things-for-sharepoint-developers.aspx To get more information about the recently published MSDN article and a tool to check the un-disposed objects. Thanks, Sowmyan

  • Anonymous
    February 06, 2009
    Just curious..Have you seen any implementation where a Web had arund 1000 lists?

  • Anonymous
    March 20, 2009
    Yes Raja.

  • Anonymous
    June 29, 2009
    Great article Sowmyan! SPWeb.Lists[Guid] is much more efficient and faster both from end user response time and well as the DB reads and DB duration (From SQL Trace). Could you please confirm? Best Regards Seeni

  • Anonymous
    June 29, 2009
    Thanks Seeni. if we pass GUID then since GUID is an unique identifier, it will be fast to retrieve it from the database table by passing it.

  • Anonymous
    July 06, 2009
    Great, very helpful. But how can I compare splistitem with eachother. I'm trying to send email to the list as long as they are not the same.(Forexample my list is based on meetingAgenda. I might have 2 meetings with same exact Agenda => only 2 email to be sent not 2). I appriciate any help you could provide. Thanks in advance.

  • Anonymous
    July 06, 2009
    The comment has been removed

  • Anonymous
    July 07, 2009
    Thank you for speedy respond! I have to look at all the SPListItems that are in the same list and show/send email to only those items that are not repeatetive. Imagine my splist contains of 4 rows. Columns headers are: "Meeting type", "Meeting Date", "Assigned to". Based on above, let's say: 1st row: "Technology", "8/7/09", "user 1" 2nd row: "Business", "6/7/9", "user 1" 3rd row: "Technology", "8/7/09", "user 3" 4th row: "Technology", "8/7/09", "user 4" The out put of my program is this: sends an email to all above items. (4 emails go out). But, what I want is only 2 emails go out. Beacause row 1,3 and 4 are the same (therefore 1 email for all.) I really appreciate your help or your thoughts! Thank you.

  • Anonymous
    July 24, 2009
    Nacny, I couldn't reply soon bcz I haven't noticed the comments email ealier. An easy way would be, use SPListItemCollection's GetDatatable method to convert all the list items to a Datatable and find out the duplicate rows using any of the methods mentioned in the below article: http://stackoverflow.com/questions/340223/what-is-the-best-way-to-remove-duplicates-from-a-datatable After that keep a list of all the ID column vlalue and then delete the list items which are duplicated. Thanks, Sowmyan

  • Anonymous
    August 17, 2009
    Hi Sowmyan Nice article... Will you pls tell me the best method to add listItem in List. generally we used SpListItem item = list.Items.Add(); item.Update();

  • Anonymous
    October 24, 2009
    as mentioned it will be better if you use the following method. SPListItemCollection oItems = list.Items SPListItem oItem = oItems.Add() oItem.Update()

  • Anonymous
    April 13, 2010
    The comment has been removed

  • Anonymous
    May 07, 2012
    Hy very helpful Site. Thanks for that However, I don´t aggree in the case of SPList.Items[Int32]. This takes an int as the Index to get the specific Item, while SPList.getItemByID(int32) gets the Item with a specific ID. So SPList.Items[0] will return the first item of the List without any further restrictions, while SPList.GetItemById(0) will return the Item with Id 0, if it exists.

  • Anonymous
    November 27, 2012
    Great post. It is very helpfull!

  • Anonymous
    November 28, 2012
    A little late to the party but another question. The url for document libraries and lists are different even though they are all SPList objects: Doc Lib: siteUrl/DocLibName vs List:  siteUrl/Lists/ListName (Note the extra "Lists/") I'm having trouble finding a good way to account for this when using the SPWeb.GetList function. I don't want to have to hard code the entire url every time. Any recommendations? Also, is there any way to effiiciently retrieve an SPList object based just on the list Title property? Obviously you've identified SPWeb.Lists[ListName] as being inefficient but there must be some way to get the list from the title in an efficient way.

  • Anonymous
    September 25, 2013
    The comment has been removed

  • Anonymous
    January 14, 2016
    Wonderful post, very helpful, thank you for posting this.