List Throttling in SharePoint 2010

1. Request throttling can be used to set the maximum retrieval limit level of the list items from the database.
2. Best technique to handle large data like when the size of folders and items increases.
3. One of the best techniques to optimize the performance.
4. The List view threshold limit is 5000 for users and 20,000 for auditors and administrators.
5. Possible to set the “Request throttling” limit is at the web applications level only [Go to Administration –> Application Management –> Manage Web Applications –> select site –> General settings menu –> select Resource Throttling].
6. The way to set the threshold limit from the Development side is by setting the “MaxItemsPerThrottledOperation” property value to the SPWebApplication.
7. But we can’t set the “Request throttling” limit value at the specific list and library level. The only possible way is either by enabling or disabling the “EnableThrottling” property of SPList. By default it is in disable state.
8. List View Lookup Threshold limit is 8 by default.

When/Where/Why Request Throttling or how it’s handle the performance:

One of the main sources of performance problem in SharePoint is either users or developers writing too many items to a list and then requesting a view or a query that tries to return a large number of items.

This causes locking on the database, and in a multiuser environment like SharePoint, this causes issues for other users who may be working in other SharePoint sites. For these reasons “List Throttling” concept is introduced.

Why Throttle Handling technique Implemented: Best example, let’s imagine that you have a list with 10,000 items in it and your throttle is 8000. You are returning only 2000 items in your views, and you try to sort on non-indexed columns to display those items. You will get an exception because SharePoint will need not return all 10000 items to sort to display your 2000 items. You can override the throttle using OM. you can do overridden in couple of ways.

• First, you can do it on an individual query, using SPQuery or SPSiteDataQuery object’s QueryThrottleMode property.
• Second, if you are farm administrator, you can use the SPList’s EnableThrotlling property to turn on/off.
• Last, you can use the OM to set the time when the throttle is off.

Exception: If the allowed number is exceeded the following message will be returned:

"Microsoft.BusinessData.Runtime.ExceededLimitException: Database Connector has throttled the response. The response from database contains more than ’1000′ rows. The maximum number of rows that can be read through Database Connector is ’1000′. The limit can be changed via the ‘Set-SPBusinessDataCatalogThrottleConfig’ cmdlet"

Like as above, we can handle External lists also. To learn External List Throttle, please refer this post.