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.


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.

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.

Tagged with:
 

21 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
    hinterlassen!

  4. Margarito says:

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

  5. Super Seite. Vielen Dank.

  6. Regards for this marvelous post, I am glad I observed this site
    on yahoo.

  7. Hey, you used to write great, but the last several posts have been kinda boring…
    I miss your great writings. Past several
    posts are just a little bit out of track! come on!

  8. I was impressed by the good writing.Thank you.
    If you want to know the social graph game, come here!

    도도그래프
    도도그래프사이트
    도도그래프먹튀
    도도그래프도메인
    도도그래프가입코드
    도도그래프코드
    도도그래프추천
    dodo그래프
    dodo그래프사이트
    해시게임사이트
    해시게임
    해시게임주소
    해시게임언오버
    해시게임도메인
    해시게임추천인
    해시게임코드
    해시게임가입코드
    https://www.holdem-79.com/dodo

    What’s Taking place i am new to this, I stumbled upon this I’ve
    discovered It absolutely helpful and it has aided me out loads.
    I’m hoping to contribute & aid other users like its aided me.
    Great job.

  9. I like this blog very much so much great info.

  10. kids clothes says:

    I wanted to construct a simple note in order to thank you for the wonderful guidelines
    you are writing at this website. My time intensive internet lookup has now been honored with sensible facts and techniques to
    go over with my family members. I would declare that we
    visitors are unequivocally endowed to dwell in a perfect site with many outstanding people with very helpful things.

    I feel rather fortunate to have encountered your website and look forward to tons of more awesome moments reading here.
    Thanks a lot once more for everything.

  11. An outstanding share! I’ve just forwarded this onto a friend who was doing a little research on this.
    And he actually ordered me breakfast due to the fact that I
    stumbled upon it for him… lol. So allow me to reword this….
    Thanks for the meal!! But yeah, thanx for spending the time to talk
    about this issue here on your blog.

  12. I like this web site because so much useful material on here :D.

  13. final food says:

    Awsome site! I am loving it!! Will come back again. I am
    bookmarking your feeds also

  14. Hello there, You’ve done a fantastic job. I will certainly digg it and for
    my part recommend to my friends. I am sure they will be benefited from this web site.

  15. Very well done & written.
    I began writibg very recently and noticed lot of articles
    simply rehash old content but add very little of value.

    It’s great to see a helpful write-up of some real value to your readers and I.

    It’s going down on my list of details I need to emulate being a new blogger.
    Visitor engagement and material value are king.
    Somee good ideas; you’vecertainly got on my list of writers to follow!

    Carry on the terrific work!
    All the best,
    Dosi

  16. Nice post. I was checking constantly this blog and I am impressed!
    Extremely useful information particularly the last part :
    ) I care for such info a lot. I was looking for this certain information for a very long time.
    Thank you and good luck.

  17. Very good blog post. I certainly love this website. Continue
    the good work!

  18. Having read this I thought it was rather enlightening. I appreciate
    you taking the time and effort to put this content together.
    I once again find myself spending a significant amount of time both reading and leaving comments.

    But so what, it was still worthwhile!

  19. Quality articles or reviews is the important to
    interest the users to pay a visit the web page, that’s
    what this site is providing.

Leave a Reply

Your email address will not be published.

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