![MCj03800210000[1]](/Lists/Posts/Attachments/61/MCj038002100001_thumb_34437094.png "MCj03800210000[1]")
Helping SharePoint Help You
A SharePoint system manages a huge amount of data. Amazingly, in a SharePoint content database, all of the data, for every list and library item, in every site and subsite, is stored in a single table. Looking at hundreds of sites, each with dozens of lists and libraries, each potentially containing hundreds or thousands of items, and you end up with one massive table!
Not So Limiting After All
Everybody has heard about the so-called "2000 item limit" in SharePoint. Remember that this isn't really a limit. SharePoint is quite capable of handling lists with tens, or even hundreds, of thousands of items. The issue is the "rendering" of those items, which starts becoming perceptibly slower if you have more than 2000 items in a single view.
While the indexing discussed in this article can have a minor effect on this rendering, it really is more general, and can improve list performance across the board.
Ask any DBA how to achieve maximum performance on a huge table, while other options may also come up, at a minimum you'll always hear the word "indexing". And make no mistake - SharePoint (whether Windows SharePoint Services 3.0, or Office SharePoint Server 2007) does do a lot of indexing. But that is only dealing with the user data table as a whole.
Once SharePoint has figured out which site and list the data belongs to, normally it is pretty much done with indexing. When you perform a query - whether in code, or for a web part view - each item in the list is examined individually for a match. As the amount of information in your sites grows, this can take quite a bit of time and cause significant slowdowns (This is independent of the "2000 item limit" - see sidebar).
Fortunately, you don't have to put up with this default behavior. SharePoint gives you the additional ability to index the information within individual lists or libraries.
Look at the settings page for just about any list or library, and you will find a link for "Indexed columns":
When you click the link, you will be given the opportunity to select which columns in your list you wish to index. This is where an understanding of your information, and how you use it comes into play. While you could just click everything, that isn't usually a good idea. For each column you index, SharePoint needs to store extra information about every item in your list.
You should only select columns for indexing that you will be using to query/filter, sort, and group your list. For this list, I'll usually need to do this with the item's title (or name), who created or modified an item, and when it starts. So those are the columns I'll select to index:
Note: When setting up indexed columns, you will almost always want to include the title or name field.
Once you click OK, SharePoint will build the appropriate extra indexes. While there won't be any change to the way your list looks, you should see the performance results almost immediately. (Of course, the more items in your list, the greater the impact will be...)
One place you usually see immediate results is when you click on the context menu of a column title to change the sorting or filtering. The list of unique values builds much faster on an indexed column.
That's all there is to it! Setting up indexed columns in your SharePoint list really is that simple. Give it a shot, and you might be surprised at how much faster your SharePoint applications can be.