Programmatically Using the Object Model Override for the List View Threshold

Introduction

In this tip, we will see what are the various options we have to avoid list throttling exceptions.

Once you know the throttle is there and can catch exceptions, you may want to override the throttle, using the object model. There are a couple of ways to do this.

There are 3 different options for SPQueryThrottleOption:

• Default – Normal behavior where all users who are not web server box administrators will be subject to the List View Threshold, including users with "Full Read" or "Full Control" permissions. This is the default behavior unless otherwise specified.
• Override – If user has “Full Control” or "Full Read" permissions, the List View Threshold for Auditors and Administrators will apply to this SPQuery, and List View Lookup Threshold will not be applied. For more information on what the List View Lookup Threshold does, read this post.
• Strict – List View threshold will apply to everyone, including web server box administrators. You can use this option to make sure that your code does not cause server stress even if it is being run as the box administrator on one of the web servers, since box administrators are not subject to the thresholds so may inadvertently slow down the servers.

The default list throtelling limit in SharePoint 2013 is 5000.

To modify the default setting, go to:

Central Administration > Manage Web Applications > General Settings > Resource Throttling > List View Threshold

If we change this option, it will affect globally which is not the good way. So, we will check what are the various options we have to set from the code.

Example: Consider a where list threshold error:

try
{
using (SPSite site = new SPSite(SPContext.Current.Site.Url))
                {
                    using (SPWeb web = site.OpenWeb())
                    {
                        SPList list = web.Lists.TryGetList("PersonalInfo");
                        SPQuery query = new SPQuery();
                        // Define columns to fetch
                        query.ViewFields = "<FieldRef Name=\"Title\" /><FieldRef Name=\"Address\" />";

                        // Force to fetch only the specified columns
                        query.ViewFieldsOnly = true;
                        query.Query = "<Where><Contains><FieldRef Name=\"Address\" /><Value Type=\"Text\">Pune</Value></Contains></Where>";
                        //Define the maximum number of results for each page (like a SELECT TOP)
                        // Query for items
                        SPListItemCollection items = list.GetItems(query);
                        foreach (SPListItem item in items)
                        {
                            Console.WriteLine(item["Title"] + " : " + item["Address"]);
                        }
                    }
                }
 }
catch (SPQueryThrottledException)
{
//Retrieving all items can trigger the throttle
}

The above query will give more results if the list is large. We will see options how to work on Throttling from the code.

try
{
using (SPSite site = new SPSite(SPContext.Current.Site.Url))
                {
                    using (SPWeb web = site.OpenWeb())
                    {
                        SPList list = web.Lists.TryGetList("PersonalInfo");
                        SPQuery query = new SPQuery();
                      query.QueryThrottleMode = SPQueryThrottleOption.Override;

                        // Define columns to fetch
                        query.ViewFields = "<FieldRef Name=\"Title\" /><FieldRef Name=\"Address\" />";

                        // Force to fetch only the specified columns
                        query.ViewFieldsOnly = true;
                        query.Query = "<Where><Contains><FieldRef Name=\"Address\" />
                        <Value Type=\"Text\">Pune</Value></Contains></Where>";
                        //Define the maximum number of results for each page (like a SELECT TOP)
                                                // Query for items
                        SPListItemCollection items = list.GetItems(query);
                        foreach (SPListItem item in items)
                        {
                            Console.WriteLine(item["Title"] + " : " + item["Address"]);
                        }
                    }
                }
 }
catch (Exception ex)
{
}

Set the SPQuery.QueryThrottleMode property to SPQueryThrottleOption.Override to disable throttling for a particular query.

This is a good way to disable throttling.

Note: In order to avoid list throttling exception, precondions should be ‘Object Model override’ attribute must be ‘Yes’ and query should be executed under super user.