SharePoint folder performance

In a recent project we had the idea to use folders as a starting position for queries to optimize the performance a bit further. To set a starting folder on an SPQuery object you need to set the Folder property to the SPFolder object you wish to search.

This is what this post is about: how to get that folder (by name) in C# in a performant matter.

Let’s get down to business: what I would like to do is write a function that takes in a folder name and it returns me the SPFolder object (in the first level, not recursively), for this I have several options:

  • You can try SPList.Folders and loop over that
  • You can do SPList.RootFolder.SubFolders

The first issue you might encounter with SPList.Folders is that it actually queries the whole list, meaning each and every item in that list recursively.

The code behind the Folders property is here:

public SPListItemCollection Folders
    {
      get
      {
        SPListItemCollection listItemCollection = (SPListItemCollection) null;
        if (!this.HasExternalDataSource)
          listItemCollection = this.GetItems(new SPQuery()
          {
            ViewAttributes = "Scope=\"<strong>RecursiveAll</strong>\"",
            Query = "<Where><Eq><FieldRef Name=\"FSObjType\" /><Value Type=\"Integer\">1</Value></Eq></Where>"
          });
        return listItemCollection;
      }
    }

This means that if you have – by default – more than 5000 items you will have an SPQueryThrottledException which is something we like to avoid.

SPList.RootFolder.SubFolders obviously does not have this limitation, unless you have more than 5000 folders under your rootfolder.

The other implication of this recurveness is that querying SPList.Folders is considerably slower than going through SPList.RootFolder.SubFolders.

Here are some figures (I show the code at the end of this post):

Function

Time

 

Folders.OfType<SPListItem()>()

2,513292

ms

Folders.Cast<SPListItem>()

0,124973

ms

RootFolder.SubFolders.OfType<SPListItem()>()

0,030432

ms

RootFolder.SubFolders.Cast<SPListItem>()

0,002376

ms

 

As you can see, going through RootFolder.SubFolders is way faster. On a sidenote: use Cast() instead of OfType() to be able to query it through Linq to Objects as it is one hell of a lot faster.

The code of the test:

public static SPListItem GetFolder1(SPList list, string folderName)
        {
            var results = from f in list.Folders.OfType<SPListItem>() where f.Title.Equals(folderName, StringComparison.InvariantCultureIgnoreCase) select f;
            if (results.Count() == 1)
                return results.ToList<SPListItem>()[0];

            return null;
        }

        public static SPListItem GetFolder2(SPList list, string folderName)
        {
            var results = from f in list.Folders.Cast<SPListItem>() where f.Title.Equals(folderName, StringComparison.InvariantCultureIgnoreCase) select f;
            if (results.Count() == 1)
                return results.ToList<SPListItem>()[0];

            return null;
        }

        public static SPListItem GetFolder3(SPList list, string folderName)
        {
            var results = from f in list.RootFolder.SubFolders.OfType<SPFolder>() where f.Name.Equals(folderName, StringComparison.InvariantCultureIgnoreCase) select f;
            if (results.Count() == 1)
                return results.ToList<SPFolder>()[0].Item;

            return null;
        }

        public static SPListItem GetFolder4(SPList list, string folderName)
        {
            var results = from f in list.RootFolder.SubFolders.Cast<SPFolder>() where f.Name.Equals(folderName, StringComparison.InvariantCultureIgnoreCase) select f;
            if (results.Count() == 1)
                return results.ToList<SPFolder>()[0].Item;

            return null;
        }