Large Lists and Datasheet view in 2010

I’ve been in a never-ending battle with 2 large lists (29K items). I migrated the lists from MOSS, which took a GREAT deal of time, patience, and effort (topic of another blog post). I got the lists migrated, views setup, and permissions all where they needed to be. The views consisted of datasheet views since the users of these lists were used to doing massive uploads at once. I got it all setup and thought “job well done.” Well not really…

Shortly after everything was setup users noticed that they would be hit with the list view threshold of 5000 items. This setting is a global setting, and one that you don’t want to change. Just trust me on this. But their views worked fine in MOSS. What gives?

…taken from: http://technet.microsoft.com/en-us/library/cc262813.aspx

In MOSS there was no limit on datasheet views like there is in 2010 (note highlighted text above). My guess is that datasheet views were treated like Standard views meaning it would filter on un-indexed columns no problem. SharePoint 2010 queries a little differently than MOSS did so if you have a filter in a datasheet view that should return 100 items based on an un-indexed column, but the list has 10,000 items, you’ll hit the list view threshold since it is querying all 10,000 items. Adding an index will alleviate this issue.

You’ll want to index the column that the view is filtering on. In this case it was the “Status” column. I added the index, SharePoint crunched on it for about 5 seconds, and then I tested it with several real users and a test user. The lists are as good as new!