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.

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.

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