.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Articles >> SharePoint >> Post New Resource Bookmark and Share   

 Subscribe to Articles

Programmatically Using the Object Model Override for the List View Threshold

Posted By:Sagar Pardeshi       Posted Date: July 15, 2014    Points: 200    Category: SharePoint    URL: http://www.dotnetspark.com  

Programmatically Using the Object Model Override for the List View Threshold
 

Introduction

 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.

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 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 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)
{
}

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



 Subscribe to Articles

     

Further Readings:

Responses

No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend