Override List View Threshold using SharePoint server object model Programmatically

This SharePoint tutorial, we will discuss how to Override List View Threshold using SharePoint server object model Programmatically. The code will work for SharePoint 2010/2013/2106.

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.

Override List View Threshold Programmatically in SharePoint

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 ListView 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 ListView Threshold for Auditors and Administrators will apply to this SPQuery, and ListView Lookup Threshold will not be applied. For more info on what the List View Lookup Threshold does, read this post.
  • Strict – List View threshold will apply for 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 it may inadvertently slow down the servers.

The default list throttling 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 a 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.RowLimit = 10;
// 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 on 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.RowLimit = 10;
// Query for items
SPListItemCollection items = list.GetItems(query);
foreach (SPListItem item in items)
{
Console.WriteLine(item["Title"] + " : " + item["Address"]);
}
}
}
}
catch (Exception ex)
{
}

Setting 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, preconditions should be ‘Object Model override’ attribute must be ‘Yes’ and query should be executed under a superuser

You may like following SharePoint server object model tutorials:

Here, we learned how to Override List View Threshold using the SharePoint server object model Programmatically in SharePoint 2013/2016.

Donwload Hub site pdf

Download SharePoint Online Tutorial PDF FREE!

Get update on Webinars, video tutorials, training courses etc.

  • >