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

Software Development Literacy – Wave of the Future or Doomsday Device?

A few months ago, I read a newspaper article – which unfortunately I can’t find – about the idea that software development literacy may someday seem as normal as reading literacy is today. I didn’t think it was far-fetched at all. In today’s world *everyone* touches a computer in some way, even if it’s only the chip that runs the fare collector on public transportation. (This isn’t a discussion about rich and poor – I tried to come up with the most benign example I could. Admittedly, it’s more a first world example.)

Today there was an article in the Boston Globe about a company called FreeCause here in Boston that is doing something unique. The story explained that…

…29-year-old company chief executive Michael Jaconi told all 60 of his employees that they had to learn the programming language JavaScript. The idea is not to turn everyone into an engineer, but to give employees — from accountants to designers to salespeople — a better understanding of what goes into developing the company’s software.

Jaconi’s initiative is a recognition that technology has inserted itself into almost every aspect of modern life, and it’s a subject people increasingly need to know. In many companies, technology often creates barriers that separate technical from nontechnical workers.   “There’s a pretty big divide between engineers and nonengineers, and what I wanted to do was bring those two camps closer together,” said Jaconi, a serial entrepreneur and former political campaign worker who is learning to code along with his employees. “I thought that this would facilitate more efficiency, bring teams closer together, and ultimately make our company perform better.”

Oddly, unless I’m really out of it, there’s a bug in the example the article showed in one of the accompanying pictures. Bonus points if you spot it.

Learning JavaScript

Image from the Boston Globe Web site

I tweeted a link. (Through the wonders of HootSuite – the awesome social media tool I prefer over all the others – I also posted it to Facebook and LinkedIn at the same time.)

The fastest response I got on Twitter was from my friend Dan Antion (@DAntion):

I expected I’d hear something similar from a good number of the developers who follow me on Twitter, and eventually I did hear from quite a few with what amounted to disparaging comments about the idea. At best they were, like Dan’s, a sort of “uh-oh”.

I think it’s more complex than that initial reaction and also more important. Let me explain my thoughts.

As a consultant, I am paid to be an expert in some things. What many of my clients don’t realize, though, is that because I don’t specialize in any particular industry and I’ve been in consulting a very long time, I also have to know at least something about a lot of things: car manufacturing, stock trading, theme parks, higher education, pharmaceutical discovery, and the list goes on. (Those are all examples of real projects I’ve worked on over the years.) I have enough humility to know that I’m not an expert in fields out of my chosen one, but I have to know *something* about others in order to advise in a useful way and to write useful solutions.

Think about your major in college. Do you “do” that thing now as your everyday activity? I majored in Mathematics, and it’s pretty rare that I “do” math. I studied all kinds of things in college: psychology, chemistry, film making, rocks for jocks [geology], etc. I don’t “do” any of those things on a daily or even yearly basis. But I’ll argue with anyone who says that a liberal arts education – wherein one studies a wide range of things – doesn’t add up to a well-rounded, multi-talented individual. (Full disclosure: my major was actually called “Computer Mathematics”. The last time I came up with an interesting, computer-based  way to factor primes was in college, though.)

Another thing I’ve seen over my years of consulting is that, generally speaking, the teams that I’ve seen be most effective share some traits. They are usually cross-functional, highly motivated, and inquisitive about each other’s knowledge. I’d take a team with those traits over specific, homogeneous knowledge any day. Note that I mentioned “inquisitive about each other’s knowledge”. That means that they want to learn a little something about what the others know. This helps them to work together more effectively.

As software development becomes more and more pervasive, what’s wrong with everyone having basic literacy in it?

We might be able to interact with technical customer support better. We may be able to understand what to do or not do to avoid infecting our computers with viruses. We may be able to save unending time by not doing things that cause our work to be lost, requiring us to recreate it. We might understand what we’re asking each other for just a little bit better, making us more able to collaborate on the important parts of the task at hand rather than level setting every time.

Simple programming knowledge (I almost said “basic programming knowledge”, but that would be too specific) is an excellent idea. To apply knowledge management principles to “using a computer”, if we can identify what the key things the high performers know that make them good at it and can teach the low performers just a scintilla of that knowledge, everyone’s competency rises. By knowing something about what’s going on under the hood, I posit we all become better digital denizens.

Also note that nothing in the article said that the accountant or the salesperson has to become a software developer. They just have to learn the basics – enough for “every FreeCause employee develop a product such as a Web page or toolbar component that could potentially be integrated into the company’s loyalty rewards software.” That’s potentially. Not definitely, and not absolutely.

I’m going to go with Jaconi’s idea as a wave of the future, and one I welcome. There’s plenty of other stuff to worry about in the doomsday category, and this isn’t one of them.

Enhanced by Zemanta

Refreshing a Web Part Without a Postback Using jQuery’s AJAX Function

I’ve been working on several projects with good old SharePoint 2007 (MOSS). jQuery and jQueryUI sure can spice it up nicely. Recently, I decided I wanted to refresh the contents of a Data View Web Part (DVWP) based on a user selection – a simple click on one of a big set of links – but I wanted to avoid that clunky postback.

SharePoint 2010 has the ManualRefresh capability baked right into DVWPs, but not SharePoint 2007. Besides, I wanted both the trigger for the refresh and the UX to work differently than the rather utilitarian (and never used in my experience) out of the box feel that 2010 gives you.

This is the script I came up with. In this case, I decided to show a nice message in a modal dialog using jQueryUI while I waited for the .ajax() call to complete, as I’m using an AggregateDataSource that is querying several pretty large lists. This means it takes a few seconds to come back, and the dialog saying what’s going on – not just a spinner – makes the UX more palatable.

/*
Refreshes an element's contents on a user action, showing a modal dialog during the refresh
elementId  The id of the container element
qs         The Query String to append to the current URL
title      The title to show for the dialog
msg        The message to show in the dialog
*/

function refreshElement(elementId, qs, title, msg) {

  var elementObj = $("#" + elementId);
  var infoDialog = $("<div><div>" + msg + "</div><div class='aaa-please-wait'></div></div>").dialog({
    open: function(event, ui) {
      $(".ui-dialog-titlebar-close").hide();  // Hide the close X
      $(this).css("overflow-y", "visible");   // Fix for the scrollbar in IE
    },
    autoOpen: false,
    title: title,
    modal: true
  });
  infoDialog.dialog("open");

  elementObj.fadeOut("slow", function() {
    $.ajax({
      async: false,
      url: window.location.pathname + qs,
      complete: function (xData) {
        newHtml = $(xData.responseText).find("#" + elementId).html();
        elementObj.html(newHtml);
      }
    });
  }).fadeIn("slow", function() {
    infoDialog.dialog("close");
  });
}

I made the function pretty generic so that I could pass in the four arguments and use it in multiple locations if I needed to. You could really pass in the id of any element in the page. In the instance I built it for, I was passing a new value on the Query String based on what link the user clicked on, but fetching the same page with AJAX. The DVWP was set up to return different data based on the Query String parameter value.

Keep in mind that both jQuery and jQueryUI must be loaded for this to work.

The CSS for the body of the dialog is pretty simple, but here it is. The sizing worked well for me based on the image I decided to display.

.aaa-please-wait {
  height:150px;
  width:auto;
  background-image:url('/SiteCollectionImages/ajax-loader.gif');
  background-repeat:no-repeat;
  background-position:center center;
}

What do you think? Would this make a good addition to SPServices? If so, what other options would you like me to include? If I did add it to SPServices, I’d remove the dependency on jQueryUI, as I don’t want any outside dependencies other than jQuery itself.

Enhanced by Zemanta

10,000 Downloads of SPServices v0.7.1

Today, July 6 at about 4pm EDT, SPServices v0.7.1 received its 10,000th download Because I’m amused by this sort of thing, I was watching on my iPhone from my friend’s beach house.

The animated GIF below shows it as I saw it happen. Notice that Toby Mai (@GrumpyTech) tweeted me right after he did the ceremonial 10,000th download.

Because Toby was the lucky 10,000th downloader, he has received a lifetime free subscription to SPServices. Enjoy, Toby!

It’s fun to save these little milestones for SPServices here on my blog for posterity. Thanks to everyone who has helped out along the way!

SPServices 1.7.1 10000

Full Reset for Internet Explorer 9 – Really

http://twitter.com/sympmarc/status/218712378165047296

This tweet caused a lot of questions back about what I meant and how I did it. so a blog post.

I have been having a number of problems with IE9, the most annoying of which was that files I downloaded wouldn’t open or run once they were fully downloaded. The dialog at the bottom of the screen simply disappeared after the download with no joy.

image

For months now, I’ve been choosing Save As… and the clicking on the Open Folder button to run or view the downloaded file. Yeah, I usually live with these things for way too long. And it was really annoying, too.

image

LastPass keeps all my passwords for me (LastPass is awesome – check it out), so that wasn’t going to be a problem. What I would give up was a pretty significant list of stuff, but I figured it was worth it to get things working right again.

After the reset, somehow Ask.com became my default search engine, which I didn’t realize until I was looking for the original post which recommended that I reset IE9 to solve the problem. Because my history was blown away, I can’t find it again.  When I had looked a few times before, the suggestion had seemed Draconian, but I finally bit the bullet.

In the interest of education, I just did another reset and captured the steps.

Go to Tools / Internet Options / Advanced tab. At the bottom of that tab’s screen, you’ll see this section:

image

Do a few Hail Marys or whatever it is that you do in these cases, and then push the Reset button anyway. You’ll get this rather intimidating dialog:

SNAGHTML7cfad38

Just to that no one can accuse me of trashing their machine (though *someone* probably will anyway), here’s the warning behind that “How does resetting affect my computer?” link:

Reset Internet Explorer 9 settings

You can reset Windows Internet Explorer 9 settings to return them to the state they were in when Internet Explorer was first installed on your computer.

Warning
  • Resetting Internet Explorer is not reversible. After a reset, all previous settings are lost and can’t be recovered. Rather than resetting everything, you might want to reset specific settings in the Internet Options dialog box or delete your webpage history.

I figured I was going to go “all in” and I checked the “Delete personal settings” box as well, then clicked Reset once more.

After I recovered from the loud boom and the smoke cleared, I was able to see that all of the steps had completed successfully.

SNAGHTML7d46205

Not only that, but I caught a whiff of that new software smell. IE9 was new again!

My original problem with downloads is fixed, and I swear that IE is running better now. Yes, I have some settings I’ll need to fix, but I’m thinking it was worth it.