One of the main performance problems that we can witness in any type of application that has to deal with data from a database is that too much data is requested. Requesting more information than actually needed by the current use case on hand results in additional

• query overhead in the database to gather the requested information
• communication overhead between the database and the application
• memory overhead on both the database and the application

Scenario 3: Use SPQuery and SPView to only request data that you need for your use case

Looking back at the previous two posts (Scenario 1, Scenario 2) you can see that the executed SQL Statement always selected ALL items from requested SharePoint list. You can tell that by looking at the SELECT clause which says: SELECT TOP 2147483648 …

Limiting the number of returned rows

In case you only want a limited result set when accessing items in a SharePoint list you can make use of the SPQuery.RowLimit property. Here is an example:

SPQuery query = new SPQuery();
query.RowLimit = 100;
SPListItemCollection items = SPContext.Current.List.GetItems(query);
for (int itemIx=0;itemIx<items.Count;itemIx++) {
  SPListItem listItem = items[itemIx];
}

Using the SPQuery object with SPList.GetItems will result in the following SELECT clause:

SPQuery.RowLimit limits the number of records retrieved from the SharePoint Content Database

In the previous example we already limited the number of items that we want to retrieve. We however still request ALL columns that are defined in the SharePoint list. This might be ok in case we really need all columns to display to the end user or if we need all of them to perform some calculations. In most of the cases we however only need a few and not all.

Limiting the retrieved columns

There are two ways of limiting the columns to retrieve from the database:

• Use a SharePoint View: SPView
• Use the SPQuery.ViewFields property

The sample code from above can therefore be changed in the following two ways:

SPQuery query = new SPQuery(SPContext.Current.CurrentView.View);

or

SPQuery query = new SPQuery();
query.ViewFields = "<FieldRef Name='ID'/><FieldRef Name='Text Field'/><FieldRef Name='XYZ'/>";

In both scenarios the SELECT clause will only contain those fields that are defined in the SharePoint view respectively those fields that are referenced in the ViewFields property. The following illustration shows the difference in the SELECT clause:

SELECT clause only selects those columns defined in SPView or ViewFields

Conclusion

There are different ways to retrieve list data with the SharePoint Object Model. It provides mechanisms to specifically query the data that you really need in your use case. Its important to understand that the default access methods always return ALL rows and ALL columns of the underlying SharePoint list. Make use of SPView’s and SPQuery’s to filter the data that you need already on the database layer in order to reduce overall overhead and therefore improve performance and scalability of your SharePoint application.

Update: Read the summary blog of my blog series about The Top SharePoint Performance Problems. As SharePoint is built on the .NET Platform you might also be interested in my latest White Papers about Continuous Application Performance for Enterprise .NET Systems

Related posts:

1. SharePoint: Page through SharePoint lists SharePoint lists can contain thousands of items. We have all heard...
2. How to avoid the Top 5 SharePoint Performance Mistakes SharePoint is without question a fast-growing platform and Microsoft is...
3. SharePoint: List Performance – How list column indices really work under the hood Have you ever wondered what is really going on under...
4. SharePoint: Lookup value Performance In SharePoint you can define lookup columns in your lists....
5. Performance Considerations when using SharePoint Object Model The SharePoint Object Model allows external applications or hosted WebParts...