CAML vs LINQ vs SQL

Question

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/

Answer 1

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.

Answer 2

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

Answer 3

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.

Answer 4

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