SharePoint Large List Performance: SPMetal vs. SPQuery

I was recently faced with determining the storage mechanism for a custom application. The choices were ‘SharePoint lists vs. SQL tables’. The user interface of this application would be implemented as a set of webparts either way.
Page Content

Key takeaway

Use SPQuery for large lists instead of SPMetal. While testing with a list of 45K items, SPQuery performed at 0.06s compared to SPMetal’s performance at 9.98s.

Background

I was recently faced with determining the storage mechanism for a custom application. The choices were ‘SharePoint lists vs. SQL tables’. The user interface of this application would be implemented as a set of webparts either way. Now SharePoint has the advantage on UI which allows end users to easily interact with the data. SQL on the other hand takes the trophy on performance. So the choice really boiled down to ‘End-User Access vs. Performance’. I really liked the idea of users being able to interact with the data so the obvious question arose: Could SharePoint perform reasonably well when dealing with ‘large’ data sets?

Throttle Levels

What is considered ‘large’ in the SharePoint world? For end users, 5000 items is where SharePoint starts to throttle queries. Using the object model however, queries of over 20,000 items leads to an Expensive Query Exception. This behavior can be overriden by setting the SPQuery.QueryThrottleMode to SPQueryThrottleOption.Override.

Additionally, these limits can be increased from Central Admin. So in order to create a large enough data set, let’s populated a blank custom list with 45,000 rows of data by setting the title column to a unique number. Additionally, for roughly 100 of these list items, let’s set a second column’s value (Column=Campaign) to a predetermined text since that way we can pull these 100 rows as part of a query.

01 using (var context = new ClientDataContext("http://site"))
02 {
03     for (int i = 0; i < 45000; i++)
04     {
05         var newItem = new PerfListItem {Title = i.ToString()};
06             if (i >= 44900)
07                 newItem.Campaign = "ABC";
08             context.PerfList.InsertOnSubmit(newItem);
09     }
10     context.SubmitChanges();
11 }

Column Indexing

Before diving into the test results, let me briefly point out that SharePoint list columns can be indexed for better performance. When dealing with large lists, throttling kicks in on sort, where and join operations if dealing with non-indexed columns. For example, a where clause on a non-indexed column is a resource-intensive operation and SharePoint will throttle the query leading to unexpected results. The correct way to deal with large lists is to index any columns that would be used for any sorting, filtering or lookups. For the purposes of our test, I will be filtering based upon the ‘Campaign’ column so let’s add an index for that. Additionally, let’s add an index for the ‘Content Type’ column since SPMetal queries add a ContentTypeId filter to the underlying CAML.

SPMetal Test

Let me begin by saying that I’m a huge fan of SPMetal. For those who don’t know, SPMetal is a command-line tool that generates entity classes, which are primarily used in LINQ to SharePoint queries. Let’s setup this test as a simple filter query which consequently displays the count of the result set.

1 using (var context = new ClientDataContext("http://site"))
2 {
3     var res = from item in context.PerfList
4               where item.Campaign == "ABC"
5               select item;
6     Console.WriteLine(res.Count());
7 }

The result displayed a whopping count of 100 as expected and the execution time was an average of 9.98s. Test was conducted 3 times and the elapsed time was measured using System.Diagnostics.Stopwatch.

Extracting SPMetal’s underlying CAML

SPMetal’s performance of 9.98s wasn’t exactly stellar so let’s try a native CAML SPQuery instead. Since SPMetal ultimately turns LINQ queries into CAML queries, a fair test would be to try the SPMetal generated CAML query directly against the list. In order to extract the underlying query used by SPMetal, we can assign a TextWriter to the Log property of the data context.

1 var sb = new StringBuilder();
2 context.Log = new StringWriter(sb);
3 //LINQ Query
4 Console.WriteLine(sb.ToString());

This results in the following CAML-

01 <View>
02   <Query>
03     <Where>
04       <And>
05         <BeginsWith>
06           <FieldRef Name="ContentTypeId" />
07           <Value Type="ContentTypeId">0x0100</Value>
08         </BeginsWith>
09         <Eq>
10           <FieldRef Name="Campaign" />
11           <Value Type="Text">ABC</Value>
12         </Eq>
13       </And>
14     </Where>
15   </Query>
16   <ViewFields>
17     <FieldRef Name="Campaign" />
18     <FieldRef Name="ID" />
19     <FieldRef Name="owshiddenversion" />
20     <FieldRef Name="FileDirRef" />
21     <FieldRef Name="Title" />
22   </ViewFields>
23   <RowLimit Paged="TRUE">2147483647</RowLimit>
24 </View>

SPQuery Test

Finally, let’s setup the test for a native CAML SPQuery against the same list.

01 var query = new SPQuery
02                 {
03                     ViewXml = "<View><Query><Where><And><BeginsWith><FieldRef Name=\"ContentTypeId\" />" +
04                     "<Value Type=\"ContentTypeId\">0x0100</Value></BeginsWith><Eq>" +
05                     "<FieldRef Name=\"Campaign\" /><Value Type=\"Text\">ABC</Value></Eq>" +
06                     "</And></Where></Query><ViewFields><FieldRef Name=\"Campaign\" />" +
07                     "<FieldRef Name=\"ID\" /><FieldRef Name=\"owshiddenversion\" />" +
08                     "<FieldRef Name=\"FileDirRef\" /><FieldRef Name=\"Title\" /></ViewFields>" +
09                     "<RowLimit Paged=\"TRUE\">2147483647</RowLimit></View>"
10                 };
11 using (var site = new SPSite("http://site"))
12 {
13     using (SPWeb web = site.OpenWeb())
14     {
15         SPList list = web.Lists["PerfList"];
16         SPListItemCollection items =list.GetItems(query);
17         Console.WriteLine(items.Count);
18     }
19 }

The result? A surprising 0.06s on average! Again the test was conducted 3 times and elapsed the time was measured using System.Diagnostics.Stopwatch.

Machine Specs

For anyone that is interested, these tests were performed on a Windows 7 Client machine with Intel Core 2 Duo 2.66GHz & 8GB RAM.

Conclusion

I finally got the answer to my initial question: It IS possible to both leverage the UI capabilities of SharePoint while working with large data sets. It is also painfully obvious that while SPMetal can save hours upon hours of development time, it clearly comes at a price. A price that gets prohibitively expensive as the data set grows in size. The lesson here is to be cognizant of the performance implications of SPMetal while balancing the need for faster development.

Advertisements

Multi-page forms with the SharePoint Scenario framework

SharePoint 2010 provides the SharePoint Scenario framework which simplifies creating and working with multi-page forms.
Page Content

 

2011-08-20-MultiPageForms-01.jpg

Splitting long forms into multiple pages is a great idea from the usability point of view. The downside is that it requires you to create some sort of mechanism of persisting the data between the pages. The good news is that SharePoint 2010 provides you with a framework for that particular purpose. Find out how to create multi-page forms with the SharePoint Scenario framework.

WhatIs: SharePoint Scenario framework

SharePoint Scenario framework is one of many hidden gems of the SharePoint 2010 platform. Although it’s not very well documented it’s an invaluable asset in your toolbox whenever you need to implement multi-page forms.

The SharePoint Scenario framework allows you to create multi-page forms, where every page can be a separate page in SharePoint (no matter if it’s an Application Page, a Web Part page or a Publishing Page). This is great news in terms of reusability of form sections. Because every page of your form can be a separate Web Part it allows you to build your forms as composite controls rather than giant all-in-one Web Parts. Reusing a particular step across multiple forms is very easy and doesn’t require you to make any changes in the code.

How it works?

A form built using the SharePoint Scenario framework consists of two pieces: the Scenario Definition and form pages. When composing the form it’s up to you to decide whether you want to implement every page as a separate control and a separate page. The great benefit of separating form pages is that you can compose many forms with minimal effort by reusing common form sections. The downside is that it adds some maintenance as you have to manage a number of Web Parts instead of one.

Important: Before you read further you should know that the SharePoint Scenario framework requires ASP.NET Session State to work. There are some interesting resources available on using Session State with SharePoint 2010 including an article by Mark Arend and another one by Todd Carter. You should read both articles before deciding on whether the Scenario framework is suitable to use in your scenario or not.

Scenario Definition

At the foundation of a form built on the Scenario Framework is the Scenario Definition. A scenario definition is an XML file deployed to the {SharePointRoot}\TEMPLATE\Scenarios\{YourScenario} folder which describes the steps in your form (scenario).

The following code snippet shows a sample Scenario Definition for a multi-page form with two pages:

01 <?xml version="1.0" encoding="utf-8"?> 
02 <Scenario xmlns="urn:Microsoft.SharePoint.Administration" Title="Visitor Info" Description="Gather Visitor Information"
03   <Section Title="Section 1"
04     <Page
05       CurrentPageUrl="/visitor-info/Pages/default.aspx"
06       NextPageUrl="/visitor-info/Pages/display.aspx"
07       RedirectOnCancelUrl="/Pages/default.aspx" /> 
08     <Page
09       CurrentPageUrl="/visitor-info/Pages/display.aspx"
10       RedirectOnCompletionUrl="/Pages/default.aspx"
11       RedirectOnCancelUrl="/Pages/default.aspx"/> 
12   </Section
13 </Scenario>

When creating a Scenario Definition for your form there are a few rules that you have to obey. Although the exact XML definition (XSD) is stored in the Microsoft.SharePoint assembly as the Microsoft.SharePoint.Administration.ScenarioXmlConfigurationSchema.xsd resource, here are a few rules that should help you get started:

  • Every Scenario must have a Title and a Description and at least one Section
  • Every Section must have a Title and at least one Page
  • Every Page must have the CurrentPageUrl and the RedirectOnCancelUrl attributes
  • The CurrentPageUrl must be at least 7 chars long and must end with the .aspx extension
  • The RedirectOnCancelUrl must not be empty
  • The last step must have the RedirectOnCompletionUrl attribute which must not be empty

There are a few scenarios already defined in the {SharePointRoot}\TEMPLATE\Scenarios folder which you can explore to get a better idea how to create a Scenario Definition for your multi-page form.

As mentioned before, Scenario Definitions must be deployed to the {SharePointRoot}\TEMPLATE\Scenarios folder. From the project perspective you can do this by adding the Scenarios Mapped Folder to your SharePoint Project:

2011-08-20-MultiPageForms-02.jpg

Important: It is important that you place your Scenario Definition XML file in a folder. The name of that folder will be used by the form as your Scenario ID.

Using the Scenario framework

The SharePoint Scenario framework ships with a very simple API located in the Microsoft.SharePoint assembly in the Microsoft.SharePoint.Administration.SPScenarioContext class.

Whenever you want to interact with the Scenario framework you have to first retrieve a reference to the Scenario Context. You can do this by calling the SPScenarioContext.GetContext(Page, CultureInfo) method:

1 SPScenarioContext context = SPScenarioContext.GetContext(Page,

For this to work the URL of the current page must contain the scenarioId query string parameter with a valid ID pointing to one of the Scenario Definitions deployed to the {SharePointRoot}\TEMPLATE\Scenarios folder.

Once you have the reference to the Scenario context you can start working with the data. For this, the API provides you with two methods: PersistDataToSessionState(string, object) and RetrieveDataFromSessionState(string). Whenever you want to persist some data to use them further in the form, all you have to do is to make a call to the PersistDataToSessionState(string, object) method:

1 SPScenarioContext context = SPScenarioContext.GetContext(Page, CultureInfo.CurrentUICulture); 
2 if (context != null) { 
3     context.PersistDataToSessionState("myKey", someObject); 
4 }

The point to note here is that you can not only store object of primitive types such as int or string but any object that is serializable.

Once you have your object stored in the Scenario Context you will want to retrieve it at some point. For this all you have to do is to call the RetrieveDataFromSessionState(string) method:

1 SPScenarioContext context = SPScenarioContext.GetContext(Page, CultureInfo.CurrentUICulture); 
2 if (context != null) { 
3     myObject = context.RetrieveDataFromSessionState("myKey") as MyType; 
4 }

Finally when you are done working with the data you have to provide your user with the ability to move to the next or the previous step. Every page in the Scenario framework has a reference to a CompletionUrl and CancelUrl. It is up to you to make those URLs point to correct URLs to create the desired flow in your multi-page form.

You can move between steps by calling either the TransitionToTheNextPage(bool) or the TransitionToTheCancelPage(bool) method which will respectively redirect the visitor to the next or the cancel page. Using the boolean parameter you can automatically redirect users to the desired page. If you need to manipulate the URL before redirecting the user, you can pass the false value to the methods and modify the returned URL. For example:

1 SPScenarioContext context = SPScenarioContext.GetContext(Page, CultureInfo.CurrentUICulture); 
2 if (context != null) { 
3     SPUtility.Redirect(context.TransitionToNextPage(false) + "&myParam=myValue", SPRedirectFlags.Static, HttpContext.Current); 
4 }

Now you know how everything works, in theory. Let’s move things into practice.

SharePoint Scenario Framework hands-on

To illustrate the working of the SharePoint Scenario Framework and to see how all pieces fall together let’s create a simple multi-page form.

On the first page we will ask the visitor to enter his first and last name:

2011-08-20-MultiPageForms-03.jpg

On the second and the last page we will display the data entered by the visitor:

2011-08-20-MultiPageForms-04.jpg

We will create the multi-page form on a Publishing Page where every form page will be a separate Publishing Page.

Step 1: Creating the Scenario Definition

Let’s start off by creating a Scenario Definition for our multi-page form.

First let’s add the Scenarios Mapped Folder so that we can deploy our Scenario Definition using a SharePoint Package. For this we use the the Add > SharePoint Mapped Folder… SharePoint Project option:

2011-08-20-MultiPageForms-05.jpg

and we select the {SharePointRoot}\TEMPLATE\Scenarios folder:

2011-08-20-MultiPageForms-06.jpg

After adding the Scenarios Mapped Folder we create a new folder called VisitorInfo and add in it a new file called VisitorInfo.xml with the following contents:

01 <?xml version="1.0" encoding="utf-8"?> 
02 <Scenario xmlns="urn:Microsoft.SharePoint.Administration" Title="Visitor Info" Description="Gather Visitor Information"
03   <Section Title="Section 1"
04     <Page
05       CurrentPageUrl="/visitor-info/Pages/default.aspx"
06       NextPageUrl="/visitor-info/Pages/display.aspx"
07       RedirectOnCancelUrl="/Pages/default.aspx" /> 
08     <Page
09       CurrentPageUrl="/visitor-info/Pages/display.aspx"
10       RedirectOnCompletionUrl="/Pages/default.aspx"
11       RedirectOnCancelUrl="/Pages/default.aspx"/> 
12   </Section
13 </Scenario>

2011-08-20-MultiPageForms-07.jpg

Step 2: Creating wrapper class for storing data

As mentioned before in our sample form we will ask the visitor to enter his first and last name which we will display later on. Although we could store all those values separately we can create a wrapper class for it.

In your SharePoint Project add a new class and call it VisitorInfo. The following code snippet shows the contents of the class:

01 using System; 
02   
03 namespace SPScenarioContextTest { 
04     [Serializable] 
05     public class VisitorInfo { 
06         public static readonly string VisitorInfoPropertyName = "VisitorInfo"; 
07   
08         public string FirstName { get; set; } 
09         public string LastName { get; set; } 
10    
11 }

The VisitorInfoPropertName field contains the name of the key with which we will store the visitor information in the Scenario Context.

Step 3: Creating the data entry form page

Our sample form will contain two pages where the first page shows the data entry form and the second one displays the entered data. To separate the pages from each other and make it possible to reuse the pages across multiple forms we will create a separate Web Part for every form page. Let’s start by creating the first page that allows the visitor to enter his personal information.

2011-08-20-MultiPageForms-08.jpg

Add to your SharePoint Project a new Web Part using the Web Part SPI Template. Call the Web Part DataWriterWebPart and paste the following contents:

01 using System; 
02 using System.ComponentModel; 
03 using System.Globalization; 
04 using System.Web.UI; 
05 using System.Web.UI.WebControls; 
06 using System.Web.UI.WebControls.WebParts; 
07 using Microsoft.SharePoint.Administration; 
08   
09 namespace SPScenarioContextTest.DataWriterWebPart { 
10     [ToolboxItemAttribute(false)] 
11     public class DataWriterWebPart : WebPart { 
12         TextBox FirstName; 
13         TextBox LastName; 
14         IButtonControl SubmitButton; 
15         IButtonControl CancelButton; 
16   
17         protected override void CreateChildControls() { 
18             FirstName = new TextBox(); 
19             Controls.Add(FirstName); 
20   
21             LastName = new TextBox(); 
22             Controls.Add(LastName); 
23   
24             SubmitButton = new Button(); 
25             SubmitButton.Text = "Submit"; 
26             SubmitButton.Click += new EventHandler(SubmitButton_Click); 
27             Controls.Add((Button)SubmitButton); 
28   
29             CancelButton = new Button(); 
30             CancelButton.Text = "Cancel"; 
31             CancelButton.Click += new EventHandler(CancelButton_Click); 
32             Controls.Add((Button)CancelButton); 
33   
34             ChildControlsCreated = true; 
35        
36   
37         void CancelButton_Click(object sender, EventArgs e) { 
38             SPScenarioContext context = SPScenarioContext.GetContext(Page, CultureInfo.CurrentUICulture); 
39             if (context != null) { 
40                 context.TransitionToCancelPage(true); 
41            
42        
43   
44         void SubmitButton_Click(object sender, EventArgs e) { 
45             SPScenarioContext context = SPScenarioContext.GetContext(Page, CultureInfo.CurrentUICulture); 
46             if (context != null) { 
47                 context.PersistDataToSessionState(VisitorInfo.VisitorInfoPropertyName, new VisitorInfo { 
48                     FirstName = FirstName.Text, 
49                     LastName = LastName.Text 
50                 }); 
51                 context.TransitionToNextPage(true); 
52            
53        
54   
55         protected override void RenderContents(HtmlTextWriter writer) { 
56             new Label { 
57                 AssociatedControlID = FirstName.ID, 
58                 Text = "First Name:"
59             }.RenderControl(writer); 
60             FirstName.RenderControl(writer); 
61             writer.Write("<br />"); 
62             new Label { 
63                 AssociatedControlID = LastName.ID, 
64                 Text = "Last Name:"
65             }.RenderControl(writer); 
66             LastName.RenderControl(writer); 
67             writer.Write("<br />"); 
68             ((Button)SubmitButton).RenderControl(writer); 
69             ((Button)CancelButton).RenderControl(writer); 
70        
71    
72 }

As you have seen, our data entry control consists of two text boxes and two buttons. Those are defined in lines 12 – 15 and instantiated in the CreateChildControls method in lines 17 – 35. Both buttons are associated with event handlers (lines 26 and 31) which interact with the SharePoint Scenario framework.

After the visitor has entered his information and clicked the Save button, his data should be persisted and he should be redirected to the next page. To do this we first have to get a reference to the Scenario Context (line 45). Once we have it, we create an instance of the VisitorInfo class (lines 47 – 50) and store it in the Scenario Context (line 47). Finally we redirect the visitor to the next page of the form (line 51).

Should the visitor click the Cancel button then he should be redirected to the cancel page. We can do this by retrieving the Scenario context and redirecting the visitor to the Cancel page (line 40).

Step 4: Creating the data display form page

The last step is to create the data display form page which retrieves the previously entered data and displays it to the visitor.

2011-08-20-MultiPageForms-09.jpg

Add another Web Part to your SharePoint Project using the Web Part SPI Template. Name the Web Part DataReaderWebPart and paste the following contents:

01 using System; 
02 using System.ComponentModel; 
03 using System.Globalization; 
04 using System.Web.UI; 
05 using System.Web.UI.WebControls; 
06 using System.Web.UI.WebControls.WebParts; 
07 using Microsoft.SharePoint.Administration; 
08   
09 namespace SPScenarioContextTest.DataReaderWebPart { 
10     [ToolboxItemAttribute(false)] 
11     public class DataReaderWebPart : WebPart { 
12         IButtonControl FinishButton; 
13         IButtonControl CancelButton; 
14   
15         VisitorInfo visitorInfo; 
16   
17         protected override void CreateChildControls() { 
18             FinishButton = new Button(); 
19             FinishButton.Text = "Finish"; 
20             FinishButton.Click += new EventHandler(FinishButton_Click); 
21             Controls.Add((Button)FinishButton); 
22   
23             CancelButton = new Button(); 
24             CancelButton.Text = "Cancel"; 
25             CancelButton.Click += new EventHandler(CancelButton_Click); 
26             Controls.Add((Button)CancelButton); 
27   
28             ChildControlsCreated = true; 
29        
30   
31         void CancelButton_Click(object sender, EventArgs e) { 
32             SPScenarioContext context = SPScenarioContext.GetContext(Page, CultureInfo.CurrentUICulture); 
33             if (context != null) { 
34                 context.TransitionToCancelPage(true); 
35            
36        
37   
38         void FinishButton_Click(object sender, EventArgs e) { 
39             SPScenarioContext context = SPScenarioContext.GetContext(Page, CultureInfo.CurrentUICulture); 
40             if (context != null) { 
41                 context.TransitionToNextPage(true); 
42            
43        
44   
45         protected override void OnPreRender(EventArgs e) { 
46             base.OnPreRender(e); 
47   
48             SPScenarioContext context = SPScenarioContext.GetContext(Page, CultureInfo.CurrentUICulture); 
49             if (context != null) { 
50                 visitorInfo = context.RetrieveDataFromSessionState(VisitorInfo.VisitorInfoPropertyName) as VisitorInfo; 
51            
52        
53   
54         protected override void RenderContents(HtmlTextWriter writer) { 
55             if (visitorInfo != null) { 
56                 writer.Write("First Name: {0}<br/>", visitorInfo.FirstName); 
57                 writer.Write("Last Name: {0}<br/>", visitorInfo.LastName); 
58                 ((Button)FinishButton).RenderControl(writer); 
59                 ((Button)CancelButton).RenderControl(writer); 
60            
61        
62    
63 }

Just like the data entry control the data display control has two buttons (lines 12 and 13) which have to be instantiated (lines 17 – 29) and linked to event handlers (lines 20 and 25).

While loading the Web Part we try to retrieve previously stored data (lines 45 – 52). We first retrieve the Scenario Context (line 48) and try to retrieve the data (line 50). Later during the render stage (lines 54 – 61) we check if the data has been retrieved (line 55) and render it if possible (lines 56 and 57).

And that’s all! All you have to do now is to build & deploy your project, create a new subsite called Visitor Info (URL: /visitor-info), create Publishing Pages as specified in the Scenario Definition and add the Web Parts.

Important: While testing the form don’t forget to append ?scenarioId=visitorInfo to the URL. Otherwise the form won’t work correctly.

Summary

A common requirement when working with large forms is to split them into multiple steps. SharePoint 2010 provides the SharePoint Scenario framework which simplifies creating and working with multi-page forms. Using the SharePoint Scenario framework you can focus on building forms instead of implementing the plumbing for persisting data between pages. An additional benefit of using the Scenario framework is that you can separate form pages and turn them into building blocks that you can reuse across multiple forms.