Linq to SharePoint vs Camlex.NET Performance Comparison

Part1 1. First()/FirstOrDefault(), T-SQL IN, Path
Part 2. Count(), Take(), Skip(), JOIN
Part 3. Anonymous access, Getting list by URK, Cross-Site queries (rus)
Part 4. SPListItem -> LINQ, Dynamic Linq to SharePoint (rus)
Part 5. Choice and MultiChoice fields in Linq to SharePoint queries (rus)
Part 6. Linq to SharePoint vs Camlex.NET Performance Comparison

Another post about using Linq to SharePoint. This time I decided to measure the performance of retrieving data from SharePoint Lists using Linq to SharePoint and Camlex.NET.

Retrieving data from SharePoint list

To measure the performance I again used the data model declared in my second part of the post about Linq to SharePoint. I measured time spent on retrieving data with System.Diagnostics.Stopwatch class. All tests were performed several times with cold and hot starts. Queries have been the most complex and identical (as far as possible). I've used joins only in order to be able to filter data by LookupValue. I don't think this fact can greatly affect the results (Camlex.NET, doesn't support joins). Previously I generated about 20k entries in Employees list.

This is the query that was passed into Linq to SharePoint:

using (var ctx = new ZhukDataContext(siteUrl))
{
    var exEmpIds = new int?[] { 1, 2, 3, 4, 5 };
    var query = ctx.Employees
        .ScopeToFolder(string.Empty, true)
        .Where(emp => emp.SexValue == "Male")
        .Where(emp => emp.Title.Contains("Jr."))
        .Where(emp => emp.Department.Title != "IT")
        .Where(Extensions.NotEqualsAll<Employee, int?>(i => i.Id, exEmpIds))
        .OrderBy(emp => emp.Title)
        .Take(100);
    var items = query.ToList();
}

A little explanation:

  • exEmpIds - Id array of employees who should be excluded;
  • SexValue - a field of type Choice;
  • Title.Contains - call the Contains method in the CAML-query;
  • Department.Title - filtering by LookupValue;

A similar query in Camlex.NET will look less elegant::

using (var site = new SPSite(siteUrl))
{
    using (var web = site.OpenWeb())
    {
        var list = web.Lists["Employees"];
        var expressions = new List<Expression<Func<SPListItem, bool>>>();
        expressions.Add(x => (string) x["Sex"] == "Male");
        expressions.Add(x => ((string) x["Title"]).Contains("Jr."));
        expressions.Add(x => x["Department"] != (DataTypes.LookupValue) "IT");
        foreach (var empId in exEmpIds)
        {
            int id = empId.Value;
            expressions.Add(x => (int) x["ID"] != id);
        }
        var caml = Camlex.Query()
            .WhereAll(expressions)
            .OrderBy(x => x["Title"]);
        var query = new SPQuery
                        {
                            RowLimit = 100,
                            Query = caml.ToString()
                        };
        var items = list.GetItems(query)
            .Cast<SPListItem>()
            .ToList();
 
    }
}

First of all, we have to check out CAML-query, generated for retrieving data from the SharePoint list. CAML-query generated by Linq to SharePoint:

<View Scope='RecursiveAll'>
  <Query>
    <Where>
      <And>
        <And>
          <And>
            <And>
              <BeginsWith>
                <FieldRef Name="ContentTypeId" />
                <Value Type="ContentTypeId">0x010078B0DD38574940478CF9E129FCD65E9B</Value>
              </BeginsWith>
              <Eq>
                <FieldRef Name="Sex" />
                <Value Type="Choice">Male</Value>
              </Eq>
            </And>
            <Contains>
              <FieldRef Name="Title" />
              <Value Type="Text">Jr.</Value>
            </Contains>
          </And>
          <Neq>
            <FieldRef Name="DepartmentTitle" />
            <Value Type="Lookup">IT</Value>
          </Neq>
        </And>
        <And>
          <And>
            <And>
              <And>
                <Neq>
                  <FieldRef Name="ID" />
                  <Value Type="Counter">1</Value>
                </Neq>
                <Neq>
                  <FieldRef Name="ID" />
                  <Value Type="Counter">2</Value>
                </Neq>
              </And>
              <Neq>
                <FieldRef Name="ID" />
                <Value Type="Counter">3</Value>
              </Neq>
            </And>
            <Neq>
              <FieldRef Name="ID" />
              <Value Type="Counter">4</Value>
            </Neq>
          </And>
          <Neq>
            <FieldRef Name="ID" />
            <Value Type="Counter">5</Value>
          </Neq>
        </And>
      </And>
    </Where>
    <OrderBy Override="TRUE">
      <FieldRef Name="Title" />
    </OrderBy>
  </Query>
  <ViewFields>
    <FieldRef Name="Sex" />
    <FieldRef Name="CellPhone" />
    <FieldRef Name="AccessLevel" />
    <FieldRef Name="Manager" />
    <FieldRef Name="Department" />
    <FieldRef Name="ID" />
    <FieldRef Name="owshiddenversion" />
    <FieldRef Name="FileDirRef" />
    <FieldRef Name="Title" />
    <FieldRef Name="Author" />
    <FieldRef Name="Editor" />
  </ViewFields>
  <ProjectedFields>
    <Field Name="DepartmentTitle" Type="Lookup" List="Department" ShowField="Title" />
  </ProjectedFields>
  <Joins>
    <Join Type="LEFT" ListAlias="Department">
      <!--List Name: Departments-->
      <Eq>
        <FieldRef Name="Department" RefType="ID" />
        <FieldRef List="Department" Name="ID" />
      </Eq>
    </Join>
  </Joins>
  <RowLimit Paged="TRUE">100</RowLimit>
</View>

And CAML generated by Camlex.NET

<Where>
  <And>
    <And>
      <And>
        <And>
          <And>
            <And>
              <And>
                <Eq>
                  <FieldRef Name="Sex" />
                  <Value Type="Text">Male</Value>
                </Eq>
                <Contains>
                  <FieldRef Name="Title" />
                  <Value Type="Text">Jr.</Value>
                </Contains>
              </And>
              <Neq>
                <FieldRef Name="Department" />
                <Value Type="Lookup">IT</Value>
              </Neq>
            </And>
            <Neq>
              <FieldRef Name="ID" />
              <Value Type="Integer">5</Value>
            </Neq>
          </And>
          <Neq>
            <FieldRef Name="ID" />
            <Value Type="Integer">4</Value>
          </Neq>
        </And>
        <Neq>
          <FieldRef Name="ID" />
          <Value Type="Integer">3</Value>
        </Neq>
      </And>
      <Neq>
        <FieldRef Name="ID" />
        <Value Type="Integer">2</Value>
      </Neq>
    </And>
    <Neq>
      <FieldRef Name="ID" />
      <Value Type="Integer">1</Value>
    </Neq>
  </And>
</Where>

Also good. There are some drawbacks, but they can't affect the performance. And now we have to measure the performance of retrieving data. Here are my results of execution queries for cold and hot starts respectively:

It's a small difference between using Linq to SharePoint and Camlex.NET in the cold start 'cause in both cases SPSite, SPWeb, SPList, and others are being initialized. In the hot start, when these objects are already cached Linq to SharePoint retrieve data faster then Camlex.NET.

Load testing

I've measured the performance of Camlex.NET in two modes: just select items from the list and the minimum conversion SPlistItem in the Employee (mapping) (new Employee {Title = item ["Title"]}). In the first case I was trying to achieve maximum performance, and in the second one is closer to real life. At the same time, I increased the number of selectable items from 10 to 2500. Results Camlex.NET performance under load:

![](https://vitalyzhukov.azureedge.net/articles/10062/Linq2SPPerformanceCamlexLoadChart-eng.png" alt="Performance of Camlex.NET depending on the load" />

Linq to SharePoint executed queries in the other two modes. The first one is ReadOnly (DataContext.ObjectTrackingEnabled = false), the second - with the included tracking. All other parameters were identical to Camlex.NET. Results of Linq to SharePoint performance depending on the load:

Performance of Linq to SharePoint depending on the load

Performance of Linq to SharePoint depending on the load

Here is a merged chart:

Performance of Camlex.NET and Linq to SharePoint depending on the load

Performance of Camlex.NET and Linq to SharePoint depending on the load

Findings

Linq to SharePoint is not only faster but also has great potential as compared to Camlex.NET. Using Linq to SharePoint we have a collection of "business" types, and on the other hand, using Camlex.NET we have a collection of SPListItem objects that need to be mapped to the business model class.

I also want to note the effect of disabling objects tracking the performance of reading data from the SharePoint list.

Vitaly Zhukov

Vitaly Zhukov

Tech Lead, Architect, Developer, Technical Trainer, Microsoft MVP. Over 20 years of experience in system integration and software development. I specialize in designing and implementing scalable, high-performance software solutions across various industries.

You May Also Like

Call Dataverse API from SPFx Web Part

Call Dataverse API from SPFx Web Part

Provision Lists and Libraries with SPFx solution

Provision Lists and Libraries with SPFx solution

SharePoint. Drag-and-Drop File Uploading

SharePoint. Drag-and-Drop File Uploading

CSOM. Upload document

CSOM. Upload document

SharePoint List REST API. Part 2

SharePoint List REST API. Part 2

SharePoint Framework. Create Angular WebPart

SharePoint Framework. Create Angular WebPart

SharePoint List REST API. Part 1

SharePoint List REST API. Part 1

Project Server. CSOM + Custom Fields

Project Server. CSOM + Custom Fields

SharePoint 2010. Long time operation with updatable status

SharePoint 2010. Long time operation with updatable status

Linq to SharePoint. Cross site collection queries

Linq to SharePoint. Cross site collection queries

SharePoint. Getting Document Icon URL

SharePoint. Getting Document Icon URL

Linq to SharePoint. Repository pattern

Linq to SharePoint. Repository pattern

Linq to SharePoint. Part 5. Choice and MultiChoice fields

Linq to SharePoint. Part 5. Choice and MultiChoice fields

Linq to SharePoint. Part 4

Linq to SharePoint. Part 4

Linq to SharePoint. Part 3

Linq to SharePoint. Part 3

Linq to SharePoint. Part 2

Linq to SharePoint. Part 2

Linq to Sharepoint. Part 1

Linq to Sharepoint. Part 1