Working with Large Lists

Recently I have been working with large lists (10,700+ records) on a site where I only have Site Owner privileges. SharePoint protects server performance by query throttling lists above 5,000 records, although this limit can be adjusted in Central Administration.  When a user attempts to view the list he will see the following message:

Because of the extra work required by the server to query a large list, work on the table is restricted to a maintenance window defined by the administrator, and is generally outside of regular business hours.

It’s no fun working outside of work, so how can we work on a large list like this during the day? Enter the magical world of indexing.

Indexing

It is instructive to understand how indexing affects throttling. From MSDN:

The list view threshold does not apply simply to the number of results returned by your query. Instead, it restricts the numbers of database rows that can be accessed in order to complete execution of the query at the row level in the content database. For example, suppose you are working with a list that contains 10,000 items. If you were to build a query that returns the first 100 items sorted by the ID field, the query would execute without issue because the ID column is always indexed. However, if you were to build a query that returns the first 100 items sorted by a non-indexed Title field, the query would have to scan all 10,000 rows in the content database in order to determine the sort order by title before returning the first 100 items. Because of this, the query would be throttled, and rightly so—this is a resource-intensive operation. http://ift.tt/1Dh32Bi

So in other words, consider how your records can be filtered. If there is a column you can use to filter records to return less than 5,000 rows, then you’re gold! Just make that column an indexed column. Of course, because creating an index requires accessing all items in the list, you’ll have to wait for the maintenance window to create your Indexed Column.

How to Create an Indexed Column

From List Settings click on the “Indexed columns” link under the Columns section.

The next screen will show you any indices you have previously created. You can create up to 20 indices. However, each additional column index requires storage space in the database and adds some overhead to every operation to maintain the index. So you should add indexes only to columns that will be used actively for filtering in views.

Click on “Create a new index” to get to…

If you have a particularly large list you can create a secondary column to create a compound index. Compound indexes can enable you to speed up queries across related values. However, Indexing more than one column may not get the boost in performance you are expecting when using more than one indexed column in a query. You should choose your indexed columns carefully to maximize query performance while avoiding unnecessary overhead; generally you only want one index column per view/query.

How to Use an Indexed Column in a View

Once your indexed column is created you can now create views off of this column and avoid the list view threshold error message. You will utilize either the Sort or Filter sections of the Edit View properties screen.

Sort

Select an indexed column as the first sort field: 

As mentioned in the MSDN quote, the ID column is indexed by default, so as long as the Item Limit is less than 5,000 items you won’t receive the list view threshold error message.

Filter

You can also filter by an indexed column. Select “Show items only when the following is true”, then select your indexed column in the dropdown window. As you can see, indexed columns are tagged as “Indexed” under the “Indexed Columns” heading:

Next you need to specify your filtering equation:

In my case I selected “is equal to” and entered the appropriate number:

You can filter on additional equations and/or columns.

Create additional views to expose your data in chunks smaller than 5000 records.

Summary

Large lists can be intimidating if you don’t know the proper way to configure them. Remember the magic of using indexed columns and you’ll be set to shine when tasked with taming a large list.

by Darrell Houghton via Everyone's Blog Posts - SharePoint Community