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: