Override List View Threshold using SharePoint server object model Programmatically

InfoPath alternatives for form designing SharePoint

In this article 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.

Read some SharePoint tutorials on:

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 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 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 the good way. So, we will check what are the various options we have to set from the code

Example : Consider an where list throshold 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 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 super user


You May Also like the Following SharePoint Online Tutorials:

About 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.

View all posts by Sagar Pardeshi →

Leave a Reply