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 the 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 decllared in my second part of the post about Linq to SharePoint [rus]. I measured time spent on the 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 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:

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

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::

  1. using (var site = new SPSite(siteUrl))
  2. {
  3.     using (var web = site.OpenWeb())
  4.     {
  5.         var list = web.Lists["Employees"];
  6.         var expressions = new List<Expression<Func<SPListItem, bool>>>();
  7.         expressions.Add(x => (string) x["Sex"] == "Male");
  8.         expressions.Add(x => ((string) x["Title"]).Contains("Jr."));
  9.         expressions.Add(x => x["Department"] != (DataTypes.LookupValue) "IT");
  10.         foreach (var empId in exEmpIds)
  11.         {
  12.             int id = empId.Value;
  13.             expressions.Add(x => (int) x["ID"] != id);
  14.         }
  15.         var caml = Camlex.Query()
  16.             .WhereAll(expressions)
  17.             .OrderBy(x => x["Title"]);
  18.         var query = new SPQuery
  19.                         {
  20.                             RowLimit = 100,
  21.                             Query = caml.ToString()
  22.                         };
  23.         var items = list.GetItems(query)
  24.             .Cast<SPListItem>()
  25.             .ToList();
  26.  
  27.     }
  28. }

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

  1. <View Scope='RecursiveAll'>
  2.   <Query>
  3.     <Where>
  4.       <And>
  5.         <And>
  6.           <And>
  7.             <And>
  8.               <BeginsWith>
  9.                 <FieldRef Name="ContentTypeId" />
  10.                 <Value Type="ContentTypeId">0x010078B0DD38574940478CF9E129FCD65E9B</Value>
  11.               </BeginsWith>
  12.               <Eq>
  13.                 <FieldRef Name="Sex" />
  14.                 <Value Type="Choice">Male</Value>
  15.               </Eq>
  16.             </And>
  17.             <Contains>
  18.               <FieldRef Name="Title" />
  19.               <Value Type="Text">Jr.</Value>
  20.             </Contains>
  21.           </And>
  22.           <Neq>
  23.             <FieldRef Name="DepartmentTitle" />
  24.             <Value Type="Lookup">IT</Value>
  25.           </Neq>
  26.         </And>
  27.         <And>
  28.           <And>
  29.             <And>
  30.               <And>
  31.                 <Neq>
  32.                   <FieldRef Name="ID" />
  33.                   <Value Type="Counter">1</Value>
  34.                 </Neq>
  35.                 <Neq>
  36.                   <FieldRef Name="ID" />
  37.                   <Value Type="Counter">2</Value>
  38.                 </Neq>
  39.               </And>
  40.               <Neq>
  41.                 <FieldRef Name="ID" />
  42.                 <Value Type="Counter">3</Value>
  43.               </Neq>
  44.             </And>
  45.             <Neq>
  46.               <FieldRef Name="ID" />
  47.               <Value Type="Counter">4</Value>
  48.             </Neq>
  49.           </And>
  50.           <Neq>
  51.             <FieldRef Name="ID" />
  52.             <Value Type="Counter">5</Value>
  53.           </Neq>
  54.         </And>
  55.       </And>
  56.     </Where>
  57.     <OrderBy Override="TRUE">
  58.       <FieldRef Name="Title" />
  59.     </OrderBy>
  60.   </Query>
  61.   <ViewFields>
  62.     <FieldRef Name="Sex" />
  63.     <FieldRef Name="CellPhone" />
  64.     <FieldRef Name="AccessLevel" />
  65.     <FieldRef Name="Manager" />
  66.     <FieldRef Name="Department" />
  67.     <FieldRef Name="ID" />
  68.     <FieldRef Name="owshiddenversion" />
  69.     <FieldRef Name="FileDirRef" />
  70.     <FieldRef Name="Title" />
  71.     <FieldRef Name="Author" />
  72.     <FieldRef Name="Editor" />
  73.   </ViewFields>
  74.   <ProjectedFields>
  75.     <Field Name="DepartmentTitle" Type="Lookup" List="Department" ShowField="Title" />
  76.   </ProjectedFields>
  77.   <Joins>
  78.     <Join Type="LEFT" ListAlias="Department">
  79.       <!--List Name: Departments-->
  80.       <Eq>
  81.         <FieldRef Name="Department" RefType="ID" />
  82.         <FieldRef List="Department" Name="ID" />
  83.       </Eq>
  84.     </Join>
  85.   </Joins>
  86.   <RowLimit Paged="TRUE">100</RowLimit>
  87. </View>

And CAML generated by Camlex.NET

  1. <Where>
  2.   <And>
  3.     <And>
  4.       <And>
  5.         <And>
  6.           <And>
  7.             <And>
  8.               <And>
  9.                 <Eq>
  10.                   <FieldRef Name="Sex" />
  11.                   <Value Type="Text">Male</Value>
  12.                 </Eq>
  13.                 <Contains>
  14.                   <FieldRef Name="Title" />
  15.                   <Value Type="Text">Jr.</Value>
  16.                 </Contains>
  17.               </And>
  18.               <Neq>
  19.                 <FieldRef Name="Department" />
  20.                 <Value Type="Lookup">IT</Value>
  21.               </Neq>
  22.             </And>
  23.             <Neq>
  24.               <FieldRef Name="ID" />
  25.               <Value Type="Integer">5</Value>
  26.             </Neq>
  27.           </And>
  28.           <Neq>
  29.             <FieldRef Name="ID" />
  30.             <Value Type="Integer">4</Value>
  31.           </Neq>
  32.         </And>
  33.         <Neq>
  34.           <FieldRef Name="ID" />
  35.           <Value Type="Integer">3</Value>
  36.         </Neq>
  37.       </And>
  38.       <Neq>
  39.         <FieldRef Name="ID" />
  40.         <Value Type="Integer">2</Value>
  41.       </Neq>
  42.     </And>
  43.     <Neq>
  44.       <FieldRef Name="ID" />
  45.       <Value Type="Integer">1</Value>
  46.     </Neq>
  47.   </And>
  48. </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 more 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:

Performance of Camlex.NET depending on the load

Linq to SharePoint executed queries in 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

Here is merged chart:

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 collection of "business" types, and on the another hand using Camlex.NET we have collection of SPListItem objects that need to ba mapped to business model class.

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


Share

Comments