Navigate Up
Sign In
Supporters of Developer
Web

SharePoint Large List Performance: SPMetal vs. SPQuery

Item is currently unrated. Press SHIFT+ENTER to rate this item.1 star selected. Press SHIFT+ENTER to submit. Press TAB to increase rating. Press SHIFT+ESCAPE to leave rating submit mode.2 stars selected. Press SHIFT+ENTER to submit. Press TAB to increase rating. Press SHIFT+TAB to decrease rating. Press SHIFT+ESCAPE to leave rating submit mode.3 stars selected. Press SHIFT+ENTER to submit. Press TAB to increase rating. Press SHIFT+TAB to decrease rating. Press SHIFT+ESCAPE to leave rating submit mode.4 stars selected. Press SHIFT+ENTER to submit. Press TAB to increase rating. Press SHIFT+TAB to decrease rating. Press SHIFT+ESCAPE to leave rating submit mode.5 stars selected. Press SHIFT+ENTER to submit. Press SHIFT+TAB to decrease rating. Press SHIFT+ESCAPE to leave rating submit mode.

 

Key takeaway

Use SPQuery for large lists instead of SPMetal. While testing with a list of 45K items, SPQuery performed at 0.06s compared to SPMetal’s performance at 9.98s.

Background

I was recently faced with determining the storage mechanism for a custom application. The choices were ‘SharePoint lists vs. SQL tables’. The user interface of this application would be implemented as a set of webparts either way. Now SharePoint has the advantage on UI which allows end users to easily interact with the data. SQL on the other hand takes the trophy on performance. So the choice really boiled down to ‘End-User Access vs. Performance’. I really liked the idea of users being able to interact with the data so the obvious question arose: Could SharePoint perform reasonably well when dealing with ‘large’ data sets?

Throttle Levels

What is considered ‘large’ in the SharePoint world? For end users, 5000 items is where SharePoint starts to throttle queries. Using the object model however, queries of over 20,000 items leads to an Expensive Query Exception. This behavior can be overriden by setting the SPQuery.QueryThrottleMode to SPQueryThrottleOption.Override.

Additionally, these limits can be increased from Central Admin. So in order to create a large enough data set, let’s populated a blank custom list with 45,000 rows of data by setting the title column to a unique number. Additionally, for roughly 100 of these list items, let’s set a second column’s value (Column=Campaign) to a predetermined text since that way we can pull these 100 rows as part of a query.

  using (var context = new ClientDataContext("http://site"))
  {
      for (int i = 0; i < 45000; i++)
      {
          var newItem = new PerfListItem {Title = i.ToString()};
              if (i >= 44900)
                  newItem.Campaign = "ABC";
              context.PerfList.InsertOnSubmit(newItem);
      }
      context.SubmitChanges();
  }

Column Indexing

Before diving into the test results, let me briefly point out that SharePoint list columns can be indexed for better performance. When dealing with large lists, throttling kicks in on sort, where and join operations if dealing with non-indexed columns. For example, a where clause on a non-indexed column is a resource-intensive operation and SharePoint will throttle the query leading to unexpected results. The correct way to deal with large lists is to index any columns that would be used for any sorting, filtering or lookups. For the purposes of our test, I will be filtering based upon the ‘Campaign’ column so let’s add an index for that. Additionally, let’s add an index for the ‘Content Type’ column since SPMetal queries add a ContentTypeId filter to the underlying CAML.

SPMetal Test

Let me begin by saying that I’m a huge fan of SPMetal. For those who don’t know, SPMetal is a command-line tool that generates entity classes, which are primarily used in LINQ to SharePoint queries. Let’s setup this test as a simple filter query which consequently displays the count of the result set.

using (var context = new ClientDataContext("http://site"))
{
    var res = from item in context.PerfList
              where item.Campaign == "ABC"
              select item;
    Console.WriteLine(res.Count());
}

The result displayed a whopping count of 100 as expected and the execution time was an average of 9.98s. Test was conducted 3 times and the elapsed time was measured using System.Diagnostics.Stopwatch.

Extracting SPMetal’s underlying CAML

SPMetal’s performance of 9.98s wasn’t exactly stellar so let’s try a native CAML SPQuery instead. Since SPMetal ultimately turns LINQ queries into CAML queries, a fair test would be to try the SPMetal generated CAML query directly against the list. In order to extract the underlying query used by SPMetal, we can assign a TextWriter to the Log property of the data context.

var sb = new StringBuilder();
context.Log = new StringWriter(sb);
//LINQ Query
Console.WriteLine(sb.ToString());

This results in the following CAML-

<View>
  <Query>
    <Where>
      <And>
        <BeginsWith>
          <FieldRef Name="ContentTypeId" />
          <Value Type="ContentTypeId">0x0100</Value>
        </BeginsWith>
        <Eq>
          <FieldRef Name="Campaign" />
          <Value Type="Text">ABC</Value>
        </Eq>
      </And>
    </Where>
  </Query>
  <ViewFields>
    <FieldRef Name="Campaign" />
    <FieldRef Name="ID" />
    <FieldRef Name="owshiddenversion" />
    <FieldRef Name="FileDirRef" />
    <FieldRef Name="Title" />
  </ViewFields>
  <RowLimit Paged="TRUE">2147483647</RowLimit>
</View>

SPQuery Test

Finally, let’s setup the test for a native CAML SPQuery against the same list.

var query = new SPQuery
                {
                    ViewXml = "<View><Query><Where><And><BeginsWith><FieldRef Name=\"ContentTypeId\" />" +
                    "<Value Type=\"ContentTypeId\">0x0100</Value></BeginsWith><Eq>" +
                    "<FieldRef Name=\"Campaign\" /><Value Type=\"Text\">ABC</Value></Eq>" +
                    "</And></Where></Query><ViewFields><FieldRef Name=\"Campaign\" />" +
                    "<FieldRef Name=\"ID\" /><FieldRef Name=\"owshiddenversion\" />" +
                    "<FieldRef Name=\"FileDirRef\" /><FieldRef Name=\"Title\" /></ViewFields>" +
                    "<RowLimit Paged=\"TRUE\">2147483647</RowLimit></View>"
                };
using (var site = new SPSite("http://site"))
{
    using (SPWeb web = site.OpenWeb())
    {
        SPList list = web.Lists["PerfList"];
        SPListItemCollection items =list.GetItems(query);
        Console.WriteLine(items.Count);
    }
}

The result? A surprising 0.06s on average! Again the test was conducted 3 times and elapsed the time was measured using System.Diagnostics.Stopwatch.

Machine Specs

For anyone that is interested, these tests were performed on a Windows 7 Client machine with Intel Core 2 Duo 2.66GHz & 8GB RAM.

Conclusion

I finally got the answer to my initial question: It IS possible to both leverage the UI capabilities of SharePoint while working with large data sets. It is also painfully obvious that while SPMetal can save hours upon hours of development time, it clearly comes at a price. A price that gets prohibitively expensive as the data set grows in size. The lesson here is to be cognizant of the performance implications of SPMetal while balancing the need for faster development.

Categories: CAML; Libraries and Lists; Performance and Optimization; MOSS; WSS; 2007; 2010

Comments

Andrew Christen

Great Articles

This information is very useful in explaining the tradeoffs between SPMetal and SPQuery(CAML). You did a great just explaining everything.

Posted 20-Dec-2011 by Andrew Christen
Nishan

Still wondering why...

Great article and thank you for sharing!
If ultimately SPMetal converts Linq to CAML before execution, then wouldn't the execution time be about the same? Those extra 9 seconds cannot be for converting LINQ to CAML query?  What am I missing....

Posted 19-Jan-2012 by Nishan

Notify me of comments to this article

E-mail:
   

Add Comment

Title:

 
Comment:
Email:

   


Name:

 
Url: