Sdílet prostřednictvím

SharePoint 2010/2013: List view Lookup threshold uncovered

  • Článek

 

SharePoint with Large lists is common scenario in any Sharepoint deployment. While there are Several blogs / Guidance TechNet articles which exist working /Managing Large lists, My blog is particularly about the "List view lookup Threshold"

Here is a table summarizes information about resource throttles and limits that you need to be aware of. These throttles and limits are set on the Resource Throttling page in Central Administration

Threshold

or Limit

Default

value

Description

List View Lookup Threshold

  8

 

 

 

 

12**

  

Specifies the maximum number of join operations, such as those based on lookup, Person/Group, or workflow status columns.

If the query uses more than eight columns, the operation is blocked. However, it is possible to programmatically select which columns to use by using maximal view, which can be set through the object model

 

This is for SharePoint 2013 Post June 2013 CU update . Refer to Note at end of this Article for Details .

From <https://office.microsoft.com/en-in/sharepoint-server-help/manage-lists-and-libraries-with-many-items-HA102771361.aspx>

This feature limits the number of joins that a query can perform. Each lookup column in a list view causes a join with another table. Each additional lookup column in a view increases the complexity of metadata navigation and list view queries. By number of joins, I mean the number of Lookup, Person/Group, or Workflow Status fields that are included in the query. So for example, if you have a view that displays 6 lookup columns, and filters on another 3 distinct lookup columns then by default that view won't work, since the List View Lookup Threshold is 8, and the view is attempting to use 9 lookups.

Here are few observations which affect this threshold , which may cause either the request to hit the threshold limit & hence be throttled or may lead to the views showing data beyond the threshold limit configured in central Admin

1. What classifies as Lookup columns: Standard lookup columns, single-value managed metadata, multiple-value managed metadata, single-value people and group columns, and multiple-value people and group columns , Workflow Status , Created by , Modified by ( people ) are obviously counted as lookup columns .

2. Additionally following columns shows on list view also work as lookup columns , Name ( linked to Document) , Link (Edit to edit item) , Name ( linked to Document with edit menu), type ( icon linked to document)

 

clip_image002

3. We allow overriding the query throttling in following circumstances

a. User is browsing when the system is in an Unthrottled Time window ( Web application throttling settings)

clip_image003

b. The current user is a box administrator (part of local admin group on the machine where the request is served from) . Local admin privileges can also be obtained by being part of an AD group which in turn is a part of local or Domain Admins groups . you can use the following command prompt to dump the group membership of a logged on user on a particular Machine /workstation .

C:> WhoAmI /Groups

c. The user is browsing using farm admin/Application Pool account   for the Web-application .

We highly recommend that you do not increase this number beyond the Default Threshold Limit , because through thorough testing we've observed that there's a serious non-gradual performance degradation that shows up above 8/12  joins. Not only does the throughput that the server can handle drop significantly at that point, but the query ends up using a disproportionately large amount of the SQL Server's resources, which negatively affects everybody else using that same database. Here is an article which talks about more on the Performance impact https://technet.microsoft.com/en-us/library/ff608068(office.14).aspx

References:

Working with Large Lists in SharePoint 2010 - List Throttling

SharePoint 2010: How to Change the List View Threshold and Other Resource Throttling Settings

Manage lists and libraries with many items

 

 

** Note : The List view Lookup Threshold has been revised to Default of 12 in SharePoint 2013 , after applying  the June 2013 CU update . https://support.microsoft.com/kb/2817414

Any new Web-Application created will have List View Lookup Threshold set to  Default =12 , While the values on Existing Web-apps needs to be modified Manually . 

 

POST BY : RAJAN    KAPOOR [MSFT]

Comments

  • Anonymous
    December 04, 2013
    Rather than MS architecting a viable SP solution, MS forces their customers to redesign most of their functioning libraries/lists. Who doesn't have business critical lists with more than 8 lookups? Are you kidding me? That's loyalty for you, not!

  • Anonymous
    December 06, 2013
    This is a list view threshold, not a *list( threshold.  Keep in mind that lists can have more the 8 lookup columns.  However, the view is limited to 8 lookup columns.

  • Anonymous
    March 12, 2014
    we have sharepoint server machines in cloud having oct 2013 updates. still the lookup threshold is 8. how?

  • Anonymous
    April 12, 2015
    Thanks for this helpful information I agree with all points you have given to us. I will follow all of them. <a href="staygreenacademy.com/.../"> SharePoint 2013 Developer Training Online</a>

  • Anonymous
    April 30, 2015
    Thanks  for the comments . Mandar Threshold=12 would show up if you create a new Web-app at a build higher then June 2013 CU , Existing Web-apps will not have the values updated by Default.

  • Anonymous
    August 26, 2015
    Lookup Plus  for SharePoint 2013, It is more than Sharepoint Lookup. Cascaded Lookup, Filtered lookup, Cross-site Lookup /drop down/ and some controls are free.  ("Create new item" link) Visit, site: www. azu . mn

  • Anonymous
    September 23, 2015
    Thank you - very helpful!

  • Anonymous
    November 06, 2015
    Nice Blog... :D

  • Anonymous
    November 18, 2015
    Thank you for your information! Even it is possible to modify the setting to have more lookup columuns, I am wondering if some issues on performance?

  • Anonymous
    December 30, 2015
    Nice Blog.. thanks..

  • Anonymous
    February 29, 2016
    Ha Ha Ha Ha Ha Ha Ha Ha Ha Ha Ha Ha Ha Ha Ha

  • Anonymous
    March 02, 2016
    Lists are accessed through views.  I have a list with 30 lookup columns.  It became necessary as I have 30 persons with 30 different roles on each team.  Each team member is responsible for up to 11 different types of documentation.  What is suppose to be the resolution?  Simply telling me I cannot do it in a very expensive product doesn't seem to be the answer. Once my list went over the threshold of 12 (SP 2013), then it disappeared.  I was unable to open it in SharePoint.  I had to use the designer.  I can neither submit data, nor read data.

  • Anonymous
    November 16, 2016
    Thanks for this Rajan!Please note that this limitation also applies to creating templates with content. If you have more lookup coulmns than the limit in your library (not list views), then you will not be able to create templates with content from that library. templates without content works fine though.I've spent the last day troubleshooting this and your post is the final piece I needed.

  • Anonymous
    September 08, 2017
    Thanks :))

  • Anonymous
    April 18, 2018
    Rajan - Thanks for the informative post. What do you mean by "maximal view" ("However, it is possible to programmatically select which columns to use by using maximal view, which can be set through the object model"). My problem is that I can control the number of lookup columns in my view queries by providing viewfields attribute to my COM CAML Queries. However, in case of list item inserts or updates done though JS COM, the item gets inserted but then the COM returns an error. I think the error is because after insert operation, SP reads the list item back and at that time I have no way to influence it to only read certain number of columns and not run into lookup column limitation. Any thoughts?