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

Updating a Lookup Column Using SharePoint’s Lists Web Service

Updating a Lookup Column Using SharePoint’s Lists Web ServiceThe SPServices discussions are a fount of interesting information. I find myself answering all sorts of questions which, while connected to SPServices, are often just as much about how SharePoint works in general. I had two questions recently about how to update Lookup columns with UpdateListItems, and I thought they would be of general interest.

Lookup column values are stored in the format “n;#Text Value”, where n is the ID of the value in the reference list and “Text Value” is, well, the text value of the Lookup column. There are probably some good historical reasons for this having to do with Office compatibility, if you think about it. Just storing the text value means that you lose the connection to the original item in the reference list, and just storing the ID for the item would mean that you’d need expensive calls to the database every time you wanted to display the value. As for the ‘;#’ delimiter, you see that sprinkled throughout how SharePoint stores data values. It’s probably related to some very old standard of some sort, but in any case it’s highly likely to be a unique string which no one will use in their text values, so it’s a reasonable delimiter.

Update a Lookup Column Value

The first question was how to update a Lookup column value. The code for this in SPServices is pretty straightforward; the tricky bit is knowing how to structure your value.

In this example, the State column in Sales Opportunities is a Lookup column to the Title column in a list called States.

  $().SPServices({
    operation: "UpdateListItems",
    async: false,
    debug:true,
    listName: "Sales Opportunities",
    ID: 5,
    valuepairs: [["State", "2;#Rhode Island"]],
    completefunc: function (xData, Status) {
      alert(xData.responseText);
    }
  });

It also works fine to just specify the index:

    valuepairs: [["State", "2"]],

The trick is knowing what the right index is. You’ll need to figure that out by calling GetListItems on the reference list or by using some other method.

“Emptying” a Lookup Column Value

The second question was how to “empty”, or remove, a previously set value in a Lookup column.

  $().SPServices({
    operation: "UpdateListItems",
    async: false,
    debug:true,
    listName: "Sales Opportunities",
    ID: 5,
    valuepairs: [["State", ""]],
    completefunc: function (xData, Status) {
      alert(xData.responseText);
    }
  });

It’s important to keep in mind that when you are interacting with SharePoint’s XML (SOAP) Web Services, *all* of the traffic back and forth is text. There’s no such thing as a null or some of the other datatypes you might be used to in other ways of interating with the API. An empty string [“”] in text essentially *is* a null; it represents “no value”.

I wish that I could say that this little tip was foolproof in interacting with all of SharePoint’s Web Services, but I can’t. There is a *lot* of internal inconsistency across the Web Services, and in some cases, even among operations in the same Web Service. If you want to set other values to “empty” or “null”, you may need to experiment to see what works. I didn’t write ’em; I just wrapped ’em! Note that the line:

      alert(xData.responseText);

can be an absolute lifesaver. It will alert what has come back from the server as a response to the Web Service call. Even if the AJAX request is successful (Status=”success”), there may be useful (or totally indecipherable) error information in the response.

SharePoint Lookup Column Based on a Calculated Column Woes

I’ve been working on trying to improve the performance of some DVWPs I wrote about a year ago as part of an application that I inherrited from others. (Was that tactful enough?) One of the things that I’ve been trying to do is to filter on the LookupId of a Lookup column, but I couldn’t for the life of me get it to work. I’ve done this dozens of times, but in this case I was getting nowhere.

I finally figured it out. The Lookup column is based on a Calculated column, which is itself built out of two strings which are concatenated. Here’s a simple example.  If the reference list looks like this:

ID ColumnA ColumnB CalcColumn
1 Blue Cow Blue – Cow
2 Red Sheep Red – Sheep

where CalcColumn is calculated as =[ColumnA]&” – “&[ColumnB], and you create a Lookup column in another list which uses CalcColumn for its values, you’ll see this in the values:

string;#Blue – Cow
string;#Red – Sheep

where you would expect:

1;#Blue – Cow
2;#Red – Sheep

Therefore, you can’t get at the LookupId of the Lookup column because it isn’t available.  (See my post on Using the ID Value for a Lookup Column for how this *ought* to work.)

Moral of the story: Don’t use a calculated column for your Lookup column source.  Now on to some list rebuilding…

Displaying a Multi-Select Column "Nicely"

UPDATE 2010-08-26: I’ve added this template to the SPXSLT project on Codeplex. There’s a bit more explanation there.

UPDATE 2010-04-27: Shalin Parmar pointed out in a comment below that I had  a bug in the template where the separator would only be displayed between the first and second values.  I’ve made a change to the template to fix this as well as to allow markup in the separator.  Thanks, Shalin!

Here’s another little utility piece of XSL which I have used from time to time.  It takes as its parameters the multi-select column’s value and a separator string.  The template will replace every occurrence of the semi-colon (;) in the multi-select value with the separator string.  This is another example of what you can pull off with recursion in your XSL templates.

Instead of seeing something like this:

value1;value2;value3

if you pass in ‘ | ‘ as the separator, you’ll see

value1 | value2 | value3

<xsl:template name="MultiSelectDisplay">
  <xsl:param name="MultiSelectValue"/>
  <xsl:param name="MultiSelectSeparator"/>
  <xsl:choose>
    <xsl:when test="contains($MultiSelectValue, ';')">
      <xsl:value-of select="concat(substring-before($MultiSelectValue, ';'), $MultiSelectSeparator)" disable-output-escaping="yes"/>
      <xsl:call-template name="MultiSelectDisplay">
        <xsl:with-param name="MultiSelectValue" select="substring-after($MultiSelectValue, ';')"/>
        <xsl:with-param name="MultiSelectSeparator" select="$MultiSelectSeparator"/>
      </xsl:call-template>
    </xsl:when>
    <xsl:otherwise>
      <xsl:value-of select="$MultiSelectValue" disable-output-escaping="yes"/>
    </xsl:otherwise>
  </xsl:choose>
</xsl:template>

Recursive XSL Templates in DVWPs

I’ve written quite a few recursive XSL templates recently, so I thought I’d do a post explaining what they are and how useful they can be.  On my current project, we’re using many Lookup Columns in Content Types which are based on items in Custom Lists.  These Lookup Columns also are almost all multi-select, meaning that the user can select zero to n of the values to categorize the item they are working with.

One of SharePoint’s quirks is how it then stores the values that the user has selected.  Generally, it looks like this: value1;value2;value3.  No problem, really, as long as you are OK with seeing the semicolon separated values in your displays.  However, if any of the values contains an apostrophe character (‘ or &apos;), it will be stored in the column as &amp;#39;, like so: value1&amp;#39;;value2;value3   (It’s actually that string without the “amp;” part, but I can’t get WordPress to let me show it without it converting to the ‘ character.)  SharePoint natively knows how to handle this, but if you are displaying the column in a DVWP, you may want to do a little work yourself to clean things up, and this is where a recursive XSL template comes in.  (Simply adding the disable-output-escaping=”yes” parameter will make the display look right, but here I want to use the values in the column for some filtering, so the &amp;#39; is a problem.)

A recursive XSL template isn’t really all that different than a recursive function in any other language.  The template can call itself based on some condition multiple times as long as it is capable of “unwinding” itself at the end of the process.  In this example, we want to remove all of the &amp;#39; strings and replace them with the ‘ character.  We don’t know how many of these strings there might be, nor do we want to care about it; we want the template to take care of it for us.

The template below works like this.  We call it, passing in the MultiSelectValue string that may or may not have the &amp;#39; string in it.  The xsl:choose has an xsl:when condition that is true if the &amp;#39; string is present.  If it is, we call the template again with the &amp;#39; string snipped out (substring-before the string plus the apostrophe plus substring-after the string), and so on.  Once there are no more &amp;#39; strings, the template will “unwind”, passing out the final string we want each time.

<xsl:template name="FixEscapedChars">
  <xsl:param name="MultiSelectValue"/>
  <xsl:variable name="Apostrophe">'</xsl:variable>
  <xsl:choose>
    <xsl:when test="contains($MultiSelectValue, '&amp;#39;')">
      <xsl:call-template name="FixEscapedChars">
        <xsl:with-param name="MultiSelectValue" select="concat(substring-before($MultiSelectValue, '&amp;#39;'), $Apostrophe, substring-after($MultiSelectValue, '&amp;#39;')) "/>
      </xsl:call-template>
    </xsl:when>
    <xsl:otherwise>
      <xsl:value-of select="$MultiSelectValue"/>
    </xsl:otherwise>
  </xsl:choose>
</xsl:template>

Note that you can handle other encoded characters by simply adding a new xsl:when clause for each to this template.