The SharePoint 2010 “List View Lookup Threshold” and Why We Don’t Change It

I’m a huge fan of Lookup columns in SharePoint. Choice columns just don’t give me enough oomph, and Managed Metadata Columns give me plenty of oomph, but not all that I need.

With Lookup columns, I can lookup into the Title of a reference list, but also store additional information in that list which is associated with the values. SharePoint 2010 gave us the ability to “drag across” additional columns from the reference along with the “key” column (usually the Title if you have good data normalization).

So I can build reference lists that have additional columns that I want the user to see (like perhaps the client’s location in addition to just their name) and columns I don’t need them to see (like a SortOrder column I can use when I build displays in a DVWP).

However, there’s a limit on the number of Lookup columns you can have on a list. That limit is 8, and those in the know, like my pal Bjørn Furuknap (@furuknap), say that even that many lookups can be a bad idea due to the performance impact. To me, this is a ridiculously low limit, but it is what it is.

Today I went scrounging for information on how I might bump up the limit and I convinced myself that I should never touch the setting based on what I read in a white paper from our pals at Microsoft.

If you look at the section with the heading “Lookup columns and list views” (reproduced below), you’ll see that bumping up the 8 lookup column limit is a Very Bad Idea.

Should you choose to bump it up anyway, it’s a Web Application setting you can get to in Central Administration: Central Administration > Application Management > Manage Web Application > Your Web Application > General Settings > Resource Throttling.

In my particular case today, I’m relegated to using some combination of Lookup columns, Choice columns, and Managed Metadata columns. The trick will be to decide for each lookup value which causes me to give up the least in terms of flexibility.

Here’s my take on the benefits and drawbacks of each:

  • Choice columns are best for lookup values that change very infrequently and for which we have no need to store any additional data. Choice columns usually can’t be managed by end users, since they are stored in the list settings. Choice columns cannot be shared across Site Collections unless they are a part of a Content Type in the Content Type Hub.
  • Lookup columns are best for values we want to reference, but where we also want to store additional data, like Client City on the Clients list. Lookup columns are also nice since we store the items in a list and that allows them to be changed easily by end users. This can also be a drawback if you don’t lock them down well enough. Lookup columns cannot be shared across Site Collections unless they are a part of a Content Type in the Content Type Hub.
  • Managed metadata columns are best for hierarchically arranged data that doesn’t have to have any additional data associated with it. Managed metadata can be shared across Site Collections. I’ve always found the managed metadata implementation to bit a bit too obtuse, and have hoped that vNext will make it work much better from an information architecture perspective.

Tradeoffs stink.


From http://technet.microsoft.com/en-us/library/cc262813.aspx#Throttling

Lookup columns and list views

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. In addition to standard lookup columns, single-value managed metadata, multiple-value managed metadata, single-value people and group columns, and multiple-value people and group columns count as lookup columns. Adding lookup columns to a view does not cause a gradual or linear decrease in performance, rather performance is somewhat stable until after eight columns when it rapidly degrades.

The following graph shows the change in throughput as the number of lookup columns in a view increases. As you can see, the change in performance from zero to eight is rather stable, but at 10 lookup columns throughput greatly decreases. This test was performed with the list by using only one row. If a list is row wrapping, then performance degrades faster.

Chart showing lookup columns in a view throughput

The following graph shows SQL Server CPU utilization as the number of lookup columns in a view increases. As you can see, there is a significant change at 10 lookup columns. For a list that has a large number of queries, having views that contain more than eight lookup columns causes the queries to use a disproportionately large amount of SQL Server resources. We recommend that you do not change this limit to be more than eight.

Chart showing SQL CPU utilization - lookup columns

Although this decrease in performance is not for the total number of lookup columns on a list, only for the number of lookup columns in a view or query, SharePoint Workspace cannot synchronize any list that has more than eight lookup columns. This is regardless of whether the columns are used in a view or not.

List view lookup threshold

Default: 8

Existed in 2007: No

Configurable: Yes

Configuration location: Central Administration, per Web application

30 Comments

  1. Thanks Marc, great synthesis on a key performance topic. I’ll be sure to share this with my users.

    I just posted this week a question about lookups on the stackoveflow forum. One workaround that was suggested was to use a workflow to copy lookups to standard text fields. Yet another trade-off…

    My forum question was actually about single lookup vs. multi-lookups. It seems that the performance impact of multi-lookups (multiple values allowed) is more important than with single lookups. Have you noticed that too?

    Reply
    • Christophe:

      Workflows to copy lookups to standard text fields? Ugh. How would you get the data into the lookups in the first place, though? You’re still limited to 8.

      You could, however, store values in text fields by using some client side script and autosuggest or something. Either way, it gets kludgy.

      As for performance, it’s really a black box, and of course, it depends. How you want to use the data is the important question: what the views will look like, etc.

      M.

      Reply
    • David:

      To be honest, I’m not sure. However, if you read the seciotn I point out, you’ll read that the issue is that each Lookup column causes a join. I would assume that the same thing would be the case if the column is a BCS column.

      I’ve also learned that one should take the performance information from Microsoft as a set of guidelines and not hard limits (unless specified as such). For instance, there was a mythical 2000 item limit per list in SharePoint 2007. I had quite a few cases where there were tens of thousands of items in lists and they performed just fine. You really need to think through what you need to do with the data and set things up efficiently, regardless what the “limits” are.

      M.

      Reply
  2. What are the implications for reducing this setting back to 8 after it had been changed to 20 (without knowing the full impact)? Does any list using >8 lookups stop functioning?

    Thank!

    Reply
    • Kevin:

      I honestly don’t know. If you’ve already done it and things are performing all right, maybe you should leave it as is? Otherwise, I’d definitely do a test in a sandbox somewhere.

      M.

      Reply
  3. Hi Marc,

    Can you help with the following lookup problem ?

    I have a lookup called (Project Number). this lookup is only shows the project that their status =initial or active. and hide = Completed ones.

    In another list (Time Sheet) I used this project number as a lookup column, so the employees can enter their hours under the projects that shows in this Lookup (initial or active).
    the time sheet list looks like :

    Project Number Hours
    ——————– ——–
    Project p1 8

    The problem is : if an employee had entered an 8 hours worked under project P1 (while it was active), and then the project status changed to (completed), the project number disappear, it looks like the following :

    Project Number Hours
    ——————– ——–
    8

    I want the value to disappear from lookup only, but to stay in the time sheet list.

    Any help is appreciated ?

    I believe there should be a solution for this in SP-2010.

    Reply
        • It sounds like you should use the Project Code itself as the lookup, not the calculated column. You’re trying to hide things in the lookup source, but you don’t want it hidden where you are using the lookup.

          M.

          Reply
  4. Hi Marc,
    I have to use the calculated as a lookup , because the employees need to see a combined data in this lookup column which is :( project code- name- customer). It’s a pattern they used to.

    Any suggestion is really appreciated ? This bug is killing my time

    Reply
    • But somehow you’re making the Project Code disappear when the project is done. (I admit that I can’t really understand what you’re doing.)

      It seems like you’ve done this to yourself somehow, and you just need to undo it.

      M.

      Reply
  5. Ok Marc,
    Let suppose I show all the project codes in the source lookup , how I can hide the completed one later on ?
    I want to hide the project code once it’s completed, so the employee can’t add hours to the completed one . That’s the main idea of the hiding.

    Is there another way to hide values from the lookup ? My SP is standard edition .

    Reply
  6. Hi Marc,
    Did you also know that if the target list has more than 20 items, the rest is not availble in the choice box in mobile view(on Iphone or Ipad). It doesn’t matter if the option for multiple items is checked or not. Either way the limit is about 20..
    I really liked this lookup feature because of the arguments You mentioned.
    Now this is a major drawback.
    On Ipad you can force to use the desktop view where all items are shown, but on Iphone it won’t let you open the desktop version.

    Reply
    • Florisz:

      When there are 20+ items in a lookup dropdown, SharePoint switches to what I call a complex dropdown, made up of input & select elements with script to drive the behavior. But that only happens in IE, not Safari, so I’m not sure what it is you’re seeing.

      In any case, it’s not related to the threshold I talk about in this post.

      M.

      Reply
  7. Hi Marc,

    I’m working in SP_Online and trying to create a lookup column in a list “B” to “drag across” stored values from managed metadata column in a list “A”.
    In my “create lookup column” page I can pick list A to reference it, but its managed metadata column is not available.
    This forces me to ask the questions:
    1. Is this approach even supported? I don’t want users to choose terms again in that list as they have already done it in another list. I want values to be “looked-up” automatically.
    2. is it general or perhaps SP Online limitation?
    3. What’s going on? :)

    Thank you in advance for your response.

    Lukas

    Reply

Leave a Reply