Wednesday, January 30, 2013

Linq to SharePoint vs. CAML vs. SQL Performance

copyright from: blog.metrostarsystems.com


On a project I’ve just recently started I was tasked to create some SharePoint web services which would serve up data stored in lists.  Having learned recently about a new tool for SharePoint 2010 called Linq to SharePoint I decided I would give it a try rather than taking my normal approach of using CAML inside of SPQuery objects.  Linq to SharePoint is supposed to speed up the development process by creating a DAL for you based on the structure of your SharePoint lists with type safety on all of the lists and the relationships within.  Also making use of Linq to write queries means there would no need for the fairly cumbersome CAML queries which all SharePoint developers have come to know and love.
After a day or so of development with this new tool the one thing that became immediately obvious to me was how limited the actual queries could be.  Perhaps it was because of the data model/relationships of the lists but the majority of the queries I needed to run could not be done with pure Linq since Linq to SharePoint can only run queries which span across no more than two lists which was a huge problem for me since many of my queries needed to span upwards of four lists. (I probably should have noticed that in the documentation).
Query limitations aside I was able to get my code working and decided to do some due diligence and fire up a load tester to see what kind of performance these web services would provide.  What I found was pretty shocking.
After reaching around six requests per second the CPU of the WFE (web front end) would become maxed out with page response times hitting nearly 2 seconds.  This seemed like a surprisingly low amount of performance since the server it was running on had four CPUs and four GB of ram (albeit it is a VM) so I decided to see where all of that CPU time was being spent so I hooked up the Visual Studio profiler and noticed that over 80% of the time was being spent in the inner workings of the Linq to SharePoint code.
At this point I decided to see what would happen if I replaced all of the Linq to SharePoint queries with plain old CAML queries running against the SPList.GetDataTable method.  The load tester was then run and came up with these numbers:
By using CAML queries the WFE was able to handle around 20 requests per second until its CPU maxed out, quite an improvement!
Then per my bosses request I created the same data model with a plain old relational database (SQL 2008r2) to see what the performance for the web service would be like with SQL as opposed to SharePoint.
As most people would suspect this preformed by far the best with the WFE being able to handle around 50 requests per second!

Although this may not have been the most scientific test in the world it definitely shows the overhead which Linq to SharePoint introduces as well as the overhead which SharePoint lists have over plain old SQL.  The lesson I’ve taken away from this is that Linq to SharePoint is okay for smaller/simpler projects where performance is not as important as getting the development done quickly however I will be staying away from it in the future