Sometimes, when I'm bored out of my mind, I go to the Internet search engines Bing and Google and type in random queries and laugh at the search suggestions they offer. For instance, try this on both Bing and Google: type in “live tiles,” and let autocomplete tell you what most people think about live tiles. Sometimes, I do this for SharePoint as well.

I wasn't surprised, but extremely amused to see that both Bing and Google agree about SharePoint being a bit slow. You can see my search suggestions in Figure 1.

Figure 1: Bing and Google search suggestions for SharePoint

Not the least surprising! And you didn't need search engines to tell you this either. Your users will tell you the same.

It's rather interesting that SharePoint gets a bad reputation of being slow. In a previous article, I talked about various improvements that you can do at the content database level to improve the performance of SharePoint. The out-of-the-box settings in SharePoint are not the best. There are reasons for the defaults, so this is not a critique of Microsoft; they had very good reasons to do what they did. But the sad reality is that many SharePoint installations out in the wild run under the defaults and therefore SharePoint is slow.

Sure, there are things that can be done at the database level to speed things up. But there are significant improvements you can make in your server-side code as well. I hope by the end of this article, you will be absolutely shocked at some of the basic improvements you can make to gain significant improvements in your custom code performance.

Iterators

Examine the code shown in this snippet.

SPList test = site.RootWeb.Lists["Test"];
for (int i = 0; i < test.Items.Count; i++)
{
    Console.WriteLine(test.Items[i].Title);
}

The code shown in that snippet seems quite innocuous. Many of you will recognize it immediately; you're looking up a list called “Test” and you're iterating through all its items and writing out the Title of each item. Many of you probably have such code running in production already.

Well, this code is just terrible.

To really find out what the issue is with this code, you need to step through it in debug mode and examine the IntelliTrace output. You'll need Visual Studio Ultimate edition for IntelliTrace to work. If you pay close attention to the IntelliTrace output, you'll probably see an issue that will make the hair on the back of your neck stand on end. Whenever the code seems to go over test.Items in the for loop, the SharePoint Web front end makes a call to the database over and over again. In other words, the “Items” collection is re-evaluated as a very complex query every single time you reference that property.

I realize this may not be very intuitive, and it's quite easy to think of .Items as a property that is in-memory. Well it isn't. A far cleaner and better way to write this code would be as shown in this next snippet.

SPList test = site.RootWeb.Lists["Test"];
SPListItemCollection items = test.Items;
foreach (SPListItem item in items)
{
    Console.WriteLine(item.Title);
}

The code in that snippet evaluates the “Items” property just once, and therefore avoids multiple hops to the database and performs much better.

Speaking of multiple hops, the actual query being run on the database is quite insane as well. More on that in a second. First, let's talk about getting the count of items.

Getting the Item Count

Finding the number of items in a list is something you'll do quite often. Frequently, you want to run a pager, or just run some code that does some processing on all items in list. It's quite easy to get the number of items in a list, as shown in this snippet.

using (SPSite site = new SPSite(siteURL))
{
    SPList test = site.RootWeb.Lists["Test"];
    Console.WriteLine(test.Items.Count);
    Console.WriteLine(test.ItemCount);
}

The code shown in that snippet shows two approaches: one that uses Items.count, and one that uses SPList.Count. Well, the approach using Items.Count is just terrible! The issue with using Items.Count is that Items.Count makes an extra hop to the server to evaluate the Count property. Every single time your code references Items.Count, you're making a round trip to the server. Imagine if the code used a for loop bound by Items.Count instead of a foreach loop. You would have an advantage by pre-caching the items and you'd still be hopping back to the server on every loop execution. In contrast, using SPList.ItemCount is populated when you get the list. It isn't re-evaluated every single time you reference it.

Getting Items

Getting items from a list is something developers do quite often. Earlier in this article, I talked about being careful when using iterators. In that example, I used the SPList.Items property to get items in a list. Although it may be quite tempting to get items in that fashion and then iterate over them in code or using LINQ etc., that would be a terrible idea. You can verify this again via the IntelliTrace output, but just for your amusement, I have shown the SQL query being executed on the back-end content database as a result of fetching SPList.Items in Figure 2.

Figure 2: The SQL query being executed when you run SPList.Items

Don't bother reading or understanding the query shown in Figure 2. It's there just to impress you. Not only is that query huge and ugly, if you examine it further using SQL Server profiler, you will see that it's run with the worst possible input parameters, such as “Fetch up to 2 billion rows and all possible columns.” Well, you did ask for Items, and you never specified any other details, so it isn't a shock that this query would be ill formed.

A far better way of querying the object model for items is as shown in this next snippet.

SPQuery query = new SPQuery();
query.RowLimit = 100;
query.ViewFields = "<FieldRef Name='Title' />";
SPListItemCollection items2 = test.GetItems(query);
foreach (SPListItem item in items2)
{
    Console.WriteLine(item.Title);
}

By changing how you query for items using SPQuery, the query shrinks in size and complexity by tens of times. The reduction is so great that I would argue that you should never query for items directly: you should always use SPQuery.

Loading a List

When you step through your code, you should get in the habit of watching the IntelliTrace output. Also, seasoned SharePoint developers already know that some objects are heavy, because our development computers freeze for a few seconds when hydrating certain objects. One of those objects is SPList. It isn't the worst, but it's on the heavier side. There are two ways to load a list, as shown in the following snippet:

using (SPSite site = new SPSite(siteURL))
{
    SPList test = site.RootWeb.Lists["Test"];
    SPList test2 = site.RootWeb.GetListFromUrl("/Lists/Test/AllItems.aspx");
}

One approach tries to find the list using the Lists collection on SPWeb, and the second tries to load it directly from a URL. The end result in both is the same: you get the same list back. But the first approach executes a stored procedure called proc_EnumLists, which finds all lists, does a string match on the one you need, and returns the list. The second approach, on the other hand, zeroes in on the list much quicker because it uses the proc_MapUrlToListandView stored procedure.

Microsoft can change its internal SharePoint implementation at any time.

ByID is Better

Imagine that you're trying to delete a List Item. There are two ways to do so: either you can use SPListItemVersion.Delete, or you can use SPFileVersionCollection.DeleteByID(versionID). As you may have guessed already, deleting an item by ID is always better than deleting it directly on the item. The same corollary can be applied to a number of other methods, as shown in Table 1.

SPDisposeCheck

The IDisposable pattern in .NET is very important to master. The rough rule of thumb is: If you see IDisposable on an object that you created, call .Dispose on it when you're done with it. But if the object was passed in to you as an argument, don't call the Dispose method, as the caller might still need it. Unfortunately, in larger and more complex platforms, there are many situations where this may not be very obvious. To help out with that, Microsoft has written a tool called SPDisposeCheck that can be found at http://spdisposecheckstatic.codeplex.com/. (Editor's note: Codeplex shut down in July 2021; look in GitHub.) It uses static analysis of your code to determine whether SharePoint APIs have been disposed of properly. While the tool was written for SharePoint 2010, and there seem to be no plans to update it for SharePoint 2013, it's still an invaluable part of your toolset, especially if you are targeting server side code.

Summary

Writing good code is not an easy thing, especially when the underlying API is tricky and complex. Writing good code is, of course, multifaceted, and one facet is performance. Given how important performance can be in SharePoint solutions, it's of paramount importance that the items discussed in this article be second nature to you as you write server-side SharePoint code.

Of course, you could just completely avoid server-side code and write apps, which is a pretty good choice these days. More on that later.

Until then, Happy SharePointing.