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.


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


SPServices v0.7.1ALPHA13 Available for Testing

Faster and more furious, that’s what I always aim for.


I’ve been working hard on this next release of SPServices. I’ve been lucky to have some down time between projects to do it. Yes, I’m happy to have the gap so that I can do more and do it better; I’ve been able to make even more significant improvements because I’ve has the focused time. It’s a great feeling to look at my old code and know that I can do a lot better now. This version of SPServices is going to be leaner and faster than ever before. As I tweeted the other day:

Alpha13, which I just posted is, I think, a good one to think of as RC1. Before I get too busy again with client work, I want to get it out there for some of you to test. If you are an SPServices user and can do some testing, especially regression testing, I’d really appreciate it.

There’s also some new stuff that I’ve built into the last few alphas:

  • Better error messages for SPCascadeDropdowns, SPDisplayRelatedInfo, and SPFilterDropdown. If you have debug mode on and the listName is incorrect, you’ll get a clearer message.
  • Improvements to the new SPXmlToJson function. All of the documented options now work as advertised and I’ve optimized the code quite a bit.
  • SPCascadeDropdowns and SPDisplayRelatedInfo now have a matchOnId option. If matchOnId is set to true, the ID of the Lookup column is used rather than the text value. This can be handy where the same text value means different things, like the place-name of Springfield, which can be found in 34 of the US States. By default the behavior of the function remains the same, of course.
  • New operations: Lists.ApplyContentTypeToList, Lists.CreateContentType, Lists.DeleteContentType, Lists.GetListItemChanges, Lists.UndoCheckOut, Lists.UpdateContentType, SiteData.GetWeb
  • Numerous (and I do mean numerous) improvements for efficiency.

Enjoy, and let me know if you have a chance to try it out.

SPServices v0.7.1ALPHA8 Available for Testing

Yup, the alphas are coming fast and furious these days.

SPServicesI just posted a new alpha of SPServices v0.7.1. The most significant change in this alpha is the ability to have multiple child columns for a parent column in SPCascadeDropdowns. This was something I always intended it to do, but the last time I tried to work through it, probably well over a year ago, I wasn’t up to the challenge. This time I was, and I think that the function may also even be a teeny bit faster than it was in ALPHA7.

Here’s an example. In this simple list form, I’ve got a parent column called State and two child columns, one called City and the other called Second City.

By making two calls to SPCascadeDropdowns, both City and Second City are filtered based on the choice of State. I’ve also chosen to convert Second City from a “complex” dropdown to a “simple” dropdown by setting the simpleChild option to true.

  relationshipList: "Cities",
  relationshipListParentColumn: "State",
  relationshipListChildColumn: "Title",
  parentColumn: "State",
  childColumn: "City", // Child 1
  debug: true
  relationshipList: "Cities",
  relationshipListParentColumn: "State",
  relationshipListChildColumn: "Title",
  parentColumn: "State",
  childColumn: "Second City", // Child 2
  simpleChild: true,
  debug: true

Enjoy, and let me know if you have a chance to try it out.

Performance Question About Using jQuery and Web Services with Large Lists

I got a question today via Twitter and my blog that I thought I’d share:

Further to Twitter message, I need to lookup values in a large list (thousands of items). Now, list performance and architecture issues aside, my intention is to use some JQuery enabled textbox which makes web service calls to suggest matching results as the user types. That’s not complex and I’m quite happy doing that. As with every time I use JQuery, I always think "should I really be solving this problem this way?". Making multiple web service calls as a user types (even with a delay) still seems wrong. I’m just not sure there’s another way to resolve the problem. What do you think?

As you know, Eric Shupps and I traded thoughts (along with a large number of other people) on this a while back:

I think that Eric and I reached the point where we realized that we were basically both saying that jQuery doesn’t kill performance: people kill performance.

There are good ways and bad ways to come at the problem.  Let me break down my thoughts…

First of all, "large lists" are, to a large degree, SharePoint myths. The real performance hit comes when you attempt to work with or display all of the items. As with any development, no matter the platform, the language, or the requirements, you have to be smart about it.

Secondly, it’s all about the use cases.  A relatively slow page which is used a few times a day is a very different situation than a slow home page.  You always have to consider who will be doing what, how often, why, etc.

Thirdly, when you use client-side code, you’re asking the client machine to do at least some of the work for you.  It’s very important to know what your client machine profiles are likely to be. If it’s a corporate environment, you already know the machine profiles.  (Right? You’ve done an inventory and you’re keeping it up to date so that you know what you can and cannot do?) If it’s an Internet site, then do you want to support Opera 5.0 as fully as IE8? Do you want to be able to assume that client machines have more than 1Gb of RAM or can support Flash or Silverlight? etc.

Lastly, from all of my experience using jQuery with the SharePoint Web Services, I’ve found them to be extremely efficient.  Basically, if you are doing something that you would expect to be non-performant through the UI, you can expect to see similar non-performant behavior with the Web Services, though it seems to me in many cases that the Web Services can actually be more efficient.  If you think about it, this makes sense.  You’re working at a lower level than full page refreshes, which requires much more work on the server’s part.

So, I’m not trying to dodge the questions; I just wanted to give a little of my perspective going into the specific answers.

"Should I really be solving this problem this way?"

Assuming that it will meet the requirements, I say "absolutely".  If this approach is going to give your users a good experience, then do it.  There are several things you can to to mitigate potential issues.

Probably the biggest mitigation would be to cache the GetListItems results.  If you can assume (note that I’m being careful about my assumptions; I know no more that what is in the questions above) that the "large list" is unlikely to change in a material way while the user is working with this piece of functionality, then making a single Web Service call and caching the results for the life of the page (or some decent time period) will keep the Web Service calls to a minimum.

Waiting for the user to type a certain number of characters before starting to do the Web Service calls can also help.  Going out and grabbing all of the items which have a value that starts with "T" may not even really be helpful, where "Thr" might be. If you look at my $().SPServices.SPDisplayRelatedInfo, I allow you to set the number of characters for just this reason.

Oh yeah, that reminds me: also consider using my jQuery Library for SharePoint Web Services. ;+)