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.

Sagar Pardeshi

I am Developer working on Microsoft Technologies for the past 6+years. I am very much passionate about programming and my core skills are SharePoint, ASP.NET & C#,Jquery,Javascript,REST. I am running this blog to share my experience & learning with the community I am an MCP, MCTS .NET & Sharepoint 2010, MCPD Sharepoint 2010, and MCSD HTML 5,Sharepoint 2013 Core Solutions. I am currently working on Sharepoint 2010, MOSS 2007, Sharepoint 2013,Sharepoint 2013 App Dev, C#, ASP.NET, and SQL Server 2008.

  • […] the performance when we use filters in a SharePoint list. This also helps to overcome the listview threshold issue which comes when you are to retrieve more than 5000 items from a list at […]

  • >