Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

So

LINQ is a front end to CAML so of course CAML is faster, and CAML is a front end to SQL so of course SQL is faster.

However what would be a good test to see where and when to use each of these on a SharePoint site.

I was thinking of processing several huge lists with each on 5 separate pages and using a performance monitor.

But this doesn't seem like the best option, any ideas? Or does someone know what the real overheads are:

This is what I have found so far (Oh and my boss insists LINQ is faster than CAML I have to prove him wrong!)

http://blog.metrostarsystems.com/2011/10/25/linq-to-sharepoint-vs-caml-vs-sql-performance/

share|improve this question
    
Lol at "my boss insists..." The pure logic of that statement is inside out... How can LINQ be faster than CAML if it has to change the query to CAML first? That just adds another step to the same process, so of course it takes longer. Thanks for the chuckle. –  RJ Cuthbertson Aug 21 '12 at 17:01
1  
I would just show him the statement that Linq by design has to translate into CAML, so by process of translation and execution, the machine has to go further to render the same essential code. msdn.microsoft.com/en-us/library/ee535491.aspx –  Mike Aug 21 '12 at 17:11
    
I know right, this was my argument, and he just said you would think that but it isn't the case, thanks for the extra link Mike. –  Hugh Wood Aug 21 '12 at 18:19

4 Answers 4

up vote 7 down vote accepted

First of all you should just rule out the use of SQL. You should not use SQL directly against SharePoint content databases. If you ever do modifications directly using SQL that SharePoint Farm becomes unsupported for ever. Even just doing reads makes your SharePoint Farm unsupported as long as they are occuring and may cause any kind of problems as SharePoint expects to control which locks are placed on which tables.

Regarding LINQ2SP vs CAML the preformance difference in reality is very little as long as they make the same queries.

What'll make the real difference between LINQ2SP and CAML is when the queries are very different and in that case it can go both ways.

In some cases developers will not know how to make an efficient CAML query (most common case is with joins), but will be able to make a LINQ query which the provider will translate into very efficient CAML and thereby only be a little slower than the CAML generated by a good SharePoint developer, but a lot faster than the CAML generated by a bad SharePoint developer.

In some cases the LINQ provider will not know how to generate the right CAML query and in that case it unfortunately silently generates the CAML query it knows how to build and the do the rest using LINQ to objects, in that case it'll be a lot slower than CAML generated even by a bad SharePoint developer.

If performance is important then my recommendation is to only use LINQ2SP in small console applications outputting generated CAML to the log and then copy/paste that CAML into the real code while checking that it looks good.

The worst case of LINQ2SP I've seen was a simple query against a list selecting top 10 records with certain criteria sorted by a column, this generated the same CAML is you'd write yourselves including OrderBy and RowLimit. When a condition to select on a choice field was added the CAML generated didn't include that extra condition and had the OrderBy and RowLimit removed as LINQ2SP now retrieved all the elements with the original criteria, and then in memory did a selection based on the choice field, sorted the elements and picked the first 10 elements. On a huge list this meant a query taking few milliseconds when to several minutes.

share|improve this answer
    
Thank you per, some things you said where useful for my argument, but a proven test I think is the only way I am going to convince him of not killing the performance. Marked as useful, and further I agree with SQL but the theory should be it is a lot faster, you wouldn't use it but that one I think should be there just so you can use it as a benchmark. –  Hugh Wood Aug 21 '12 at 18:21

But in the end LINQ will execute the CAML, ie the advantage of using LINQ is that you will not need to write the Query strange, now the requirement for performance, I believe that CAML is faster, but has a very big difference

share|improve this answer

once i wrote a class that create caml query very easily by a simple syntax. If you dont want to lost in caml syntax, this class (i name it as cawl4SharePoint) is an option.

https://github.com/makdeniz/cawl4SharePoint

share|improve this answer

It really depends on the situation. Sure, conventional wisdom says never query in SQL. Yet this is counter to all the logic in non-SharePoint solutions. It is especially relevant with large datasets. If you take care of business in SQL, you end up with less data to send across the wire. Of course that's fastest. It is especially relevant for none SharePoint data. But it could also make sense for SharePoint data. Although Microsoft certainly does not support it. But if you want to make an omelet, you have to break a few eggs.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.