Post
Ask Question

Programmatically Using the Object Model Override For the List View Threshold

Introduction

This article explains what the various options are that we need to avoid list throttling exceptions.

Once you understand that the throttle exists 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 the following 3 options for SPQueryThrottleOption:

  • Default: The 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 the 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 article.
  • Strict: List View threshold will apply for everyone, including web server box administrators. You can use this option to ensure 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 then it will have global affect and that is not the best way. So, we will determine what are the various options we need to set it from code.

Example: Consider an where list throshold error.

  1. try  
  2. {  
  3. using (SPSite site = new SPSite(SPContext.Current.Site.Url))  
  4.                 {  
  5.                     using (SPWeb web = site.OpenWeb())  
  6.                     {  
  7.                         SPList list = web.Lists.TryGetList("PersonalInfo");  
  8.                         SPQuery query = new SPQuery();  
  9.                         // Define columns to fetch  
  10.                         query.ViewFields = "<FieldRef Name=\"Title\" /><FieldRef Name=\"Address\" />";  
  11.   
  12.                         // Force to fetch only the specified columns  
  13.                         query.ViewFieldsOnly = true;  
  14.                         query.Query = "<Where><Contains><FieldRef Name=\"Address\" /><Value Type=\"Text\">Pune</Value></Contains></Where>";  
  15.                         //Define the maximum number of results for each page (like a SELECT TOP)  
  16.                         query.RowLimit = 10;  
  17.                         // Query for items  
  18.                         SPListItemCollection items = list.GetItems(query);  
  19.                         foreach (SPListItem item in items)  
  20.                         {  
  21.                             Console.WriteLine(item["Title"] + " : " + item["Address"]);  
  22.                         }  
  23.                     }  
  24.                 }  
  25.  }  
  26. catch (SPQueryThrottledException)  
  27. {  
  28. //Retrieving all items can trigger the throttle  
  29. } 

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

  1. try  
  2. {  
  3. using (SPSite site = new SPSite(SPContext.Current.Site.Url))  
  4.                 {  
  5.                     using (SPWeb web = site.OpenWeb())  
  6.                     {  
  7.                         SPList list = web.Lists.TryGetList("PersonalInfo");  
  8.                         SPQuery query = new SPQuery();  
  9.                       query.QueryThrottleMode = SPQueryThrottleOption.Override;  
  10.   
  11.                         // Define columns to fetch  
  12.                         query.ViewFields = "<FieldRef Name=\"Title\" /><FieldRef Name=\"Address\" />";  
  13.   
  14.                         // Force to fetch only the specified columns  
  15.                         query.ViewFieldsOnly = true;  
  16.                         query.Query = "<Where><Contains><FieldRef Name=\"Address\" /><Value Type=\"Text\">Pune</Value></Contains></Where>";  
  17.                         //Define the maximum number of results for each page (like a SELECT TOP)  
  18.                         query.RowLimit = 10;  
  19.                         // Query for items  
  20.                         SPListItemCollection items = list.GetItems(query);  
  21.                         foreach (SPListItem item in items)  
  22.                         {  
  23.                             Console.WriteLine(item["Title"] + " : " + item["Address"]);  
  24.                         }  
  25.                     }  
  26.                 }  
  27.  }  
  28. catch (Exception ex)  
  29. {  
  30. } 

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

This is a good way to disable throttling.

Note: In order to avoid a list throttling exception, the preconditions for the "Object Model override" attribute must be "Yes" and the query should be executed under Super User.


Similar Articles