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.


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.

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.

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.

This results in the following CAML-

SPQuery Test

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

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.


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.

Tagged with:

6 Responses to Large list performance: SPMetal vs. SPQuery

  1. Ahmet Salih Guvenli says:

    Did you try with DataContext.ObjectTrackingEnabled set to false? I have a nagging suspicion that the performance hit you describe above is largely due to change tracking being enabled.

  2. Anonymous says:

    Well I’m facing the same problem if the items in the list goes above 1000 items , it takes about a 27 sec to save that item using SPmetal….

  3. Judith says:

    Ich bin gerade zufaellig auf Ihrer Page gelandet (war eigentlich auf
    der Suche nach einer anderen Websiete). Ich moechte diese websiete nicht verlassen, ohne
    Euch ein Lob zu dieser klar strukturierten und schick designten Page zu

  4. Margarito says:

    Klasse gemachte Homapage, das Layout gefaellt mir echt gut!
    War bestimmt ‘n haufen Arbeit.

  5. Super Seite. Vielen Dank.

Leave a Reply

Your email address will not be published.

Set your Twitter account name in your settings to use the TwitterBar Section.