SharePoint 2010 List Throtelling

Introduction

List throtelling is the behaviour which allows to configure SharePoint list to prevent any operation from returning too many results at any time.
In SharePoint 2010 one of the improvements is list throttling. In this article we will see what are the various options we have to avoid list throtelling exceptions.

List Throtelling

The default list throtelling limit in SharePoint 2010 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

ListThrotelling options

Consider an example where list throshold error will be coming

1 2 3 4 5 6 7 8 9 10 11 12 13

string query = @"<Where>                             <And>                                 <BeginsWith>                                     <FieldRef Name='Title' />                                     <Value Type='Text'>A</Value>                                 </BeginsWith>                             </And>                  </Where>"    SPQuery listQuery = new SPQuery();  listQuery.Query = query;  SPList list = SPContext.Current.Web.Lists["EmpList"];  SPListItemCollection items = list.GetItems(listQuery);

The above query will give more results if the list is large. We will see the various options how to work on throtelling from the code.

SPList.EnableThrottling

Setting the SPList.EnableThrottling property to false will disable throttling for a particular list.

In the above example if we change the code like the following it won’t give the throtteling exception

1 2 3

SPList list = SPContext.Current.Web.Lists["EmpList"]; list.EnableThrottling = false; SPListItemCollection items = list.GetItems(listQuery);

But, in practcal this option is not the good one to use as throttling is disabled for the list.

SPQuery.QueryThrottleMode

Setting the SPQuery.QueryThrottleMode property to SPQueryThrottleOption.Override to disable throttling for a particular query.
This is a good way to disable throttling.
Note: Inorder to avoid list throtteling exception, precondions should be ‘Object Model override’ attribute must be ‘Yes’ and query should be executed under super user

In the above example the code will be like

1 2 3 4 5

SPQuery listQuery = new SPQuery(); listQuery.Query = query; listQuery.QueryThrottleMode = SPQueryThrottleOption.Override; SPList list = SPContext.Current.Web.Lists["EmpList"]; SPListItemCollection items = list.GetItems(listQuery);

ContentIterator

We should consider using ContentIterator class in the Microsoft.Office.Server.Utilities for accessing large lists with throttling enabled or if the list is likely to grow in future.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

SPQuery listQuery = new SPQuery(); listQuery.Query = query; SPList list = SPContext.Current.Web.Lists["EmpList"]; string iteratorName = "EmpList Iterator"; ContentIterator ci = new ContentIterator(iteratorName); ci.ProcessListItems(list, listQuery, ItemProcessor, ErrorProcessor);   private static bool ErrorProcessor(SPListItem item, Exception e) {   //logic here to handle exceptions thrown while processing a list.       // Do not rethrow exception, keep iterating   return true; }    private static void ItemProcessor(SPListItem item)  {     // Put the code here to process a list item    }

Conclusion

List throttling is not only for restriction on retrieval of data from the list. But, also it is the restriction on data that is affected. For example if our single action deletes 5000 items of the list, then also throtelling exception will occur.
So throtelling is determined by the number of rows affected in content database.