Does number of lookup column slows down the list performance + SharePoint 2010

Saurav Kumar (Digital) 21 Reputation points
2020-09-24T16:26:24.437+00:00

Hi Team,
There are 23 lookup column(including lookup + People or group column) in SP2010 list.
We see while opening new form/display/edit form, it takes almost 30 sec from past 6 months. it was working fine from last 7 years.
I see the limit is on list VIEW lookup which is 8 so I understand in a single view(e.g. allitems.aspx) max lookup should be 8 for better performance or its on particular list that number of lookup column should not exceed 8 for better performance

Thank you for your help

Regards
Saurav

SharePoint Server Management
SharePoint Server Management
SharePoint Server: A family of Microsoft on-premises document management and storage systems.Management: The act or process of organizing, handling, directing or controlling something.
2,360 questions
No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Trevor Seward 11,491 Reputation points Microsoft MVP
    2020-09-24T17:30:49.05+00:00

    Yes, on 2010 adding >8 lookups may slow down performance (with 2013 it was increased to 12). While I can't find the graph any longer, Microsoft did present some test results on this.

    SharePoint Lists are not intended to be a relational database, which sounds like what you'd want if you have that many lookup columns. I would either look at refactoring the list or moving to a SQL-based solution with a web front end.

    No comments

  2. Echo Du_MSFT 16,836 Reputation points
    2020-09-25T03:15:10.287+00:00

    @Saurav Kumar (Digital)

    Yes, you are right. In SharePoint 2010, if the query uses more than eight columns, the operation is blocked.
    However, many limits in SharePoint Server can be changed, and thresholds and supported limits guidelines are determined by performance. In other words, you can exceed the default values of the limits, but as you increase the limit value, farm performance and the effective value of other limits may be affected.

    In SharePoint 2010 change List View Lookup Threshold:

    1.Go to SharePoint Central Admin
    2.Click Manage web apps link
    3.Select the web application for which you’re change the lookup limit
    4.Click the drop-down arrow beneath “General Settings” >> “Resource Throttling
    28059-limit1.png

    5.On the Resource Throttling dialog, edit the list view lookup limit under the List View Lookup Threshold section, and then Save it.
    28109-limit2.png

    Thanks,
    Echo Du

    ===========

    If an Answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.