none
The query cannot be completed because the number of lookup columns it contains exceeds the lookup column threshold enforced by the administrator.

    Question

  • While using farm admin account the following error is not coming "The query cannot be completed because the number of lookup columns it contains exceeds the lookup column threshold enforced by the administrator." But while using sharepoint user account including site collection owners this "The query cannot be completed because the number of lookup columns it contains exceeds the lookup column threshold enforced by the administrator." error occurs. Please help
    Monday, April 16, 2012 9:54 AM

Answers

  • Each new lookup column requires an additonal SQL join.  As the number of joins goes up it will have a serioous effect on performance.  By default the limit is 8 lookups per list.  As you have seen this limit does not apply to farm admins.  You can change the limit for other users in the Resource Throttling settings of the web application in Central Administration.  But making changes to the resource limit can seriously impact performance.


    Paul Stork SharePoint Server
    MVP Chief SharePoint Architect: Sharesquared
    Blog: http://dontpapanic.com/blog
    Twitter: Follow @pstork
    Please remember to mark your question as "answered" if this solves your problem.

    Monday, April 16, 2012 10:11 AM

All replies

  • Each new lookup column requires an additonal SQL join.  As the number of joins goes up it will have a serioous effect on performance.  By default the limit is 8 lookups per list.  As you have seen this limit does not apply to farm admins.  You can change the limit for other users in the Resource Throttling settings of the web application in Central Administration.  But making changes to the resource limit can seriously impact performance.


    Paul Stork SharePoint Server
    MVP Chief SharePoint Architect: Sharesquared
    Blog: http://dontpapanic.com/blog
    Twitter: Follow @pstork
    Please remember to mark your question as "answered" if this solves your problem.

    Monday, April 16, 2012 10:11 AM
  • Thanks dude.

    So changes in the central administration will not affect farm admin permission?? why????


    Monday, April 16, 2012 10:14 AM
  • Farm Admins are exempt from the limitation by default.  The assumption is that Farm adminsitrators should KNOW the performance problems that can be caused by large numbers of Lookup fields.  But that in some circumstances they may still need to use them.  That exemption is part of the design.

    Paul Stork SharePoint Server
    MVP Chief SharePoint Architect: Sharesquared
    Blog: http://dontpapanic.com/blog
    Twitter: Follow @pstork
    Please remember to mark your question as "answered" if this solves your problem.


    Monday, April 16, 2012 11:30 AM
  • Also, when you exceed 8 joins, you go from a record lock to a table lock in SQL so that is another reason the number is set at 8.

    http://blogs.msdn.com/b/dinaayoub/archive/2010/04/22/sharepoint-2010-how-to-change-the-list-view-threshold.aspx


    JD Wade, MCITP
    Senior SharePoint Consultant, Horizons Consulting, Inc.

    Monday, April 16, 2012 12:28 PM
  • Maybe to add some value, the Technet article on boundaries, limits and in this case thresholds on SharePoint Server 2010 (also the List view lookup threshold):
    http://technet.microsoft.com/en-us/library/cc262787.aspx

    Monday, April 16, 2012 9:45 PM
  • This is the same issue that I am seeing. The list will not open because the lookup exceeds the threshold. However, I don't have any "lookups" in my list. I have an InfoPath form linked to the list that the user Submits after completing. The column count is not extensive (~30). Why am I getting this message?

    Thanks

    Wednesday, August 08, 2012 2:42 PM
  • A point of clarification, @wilsonme10: "Lookup columns" here means not just "Lookup" column types, but any column that requires a "lookup" effort to be completed on the back-end. Because of how certain fields work, these "lookup columns" (an imprecise term that Microsoft uses to refer to these columns) includes columns "such as" managed metadata, people or group, and workflow status fields.

    I put the "such as" in quotes here because Microsoft does not seem to provide a definitive list of which columns are considered "lookup columns"...


    http://www.linkedin.com/in/corysalveson

    Monday, September 24, 2012 9:03 PM