When the number of items in a list or library grows to more than one thousand, the performance of the list or library may begin to slow down or adversely affect other areas of the site. Such issues can be caused by user actions such as sorting or retrieving a large number of items at the same time. However, you can store millions of items in lists and libraries as long as you plan and configure the list or library correctly.

The most common example of a user action affecting performance occurs when a user creates or accesses a view of the items in a very large list or library. When defining a view, you have the choice of returning all the items or filtering a subset of items based on a particular column. Any time the total number of items in a list or library is very large, it is important to limit the number of items that a user is working with at one time. This involves filtering the total set of items to a smaller number that is more manageable for the end user and for the database.

Depending on the type of list or library, you can use the organizational structure and certain features to help users to work with large numbers of items. The following sections provide tips and techniques for ensuring that users can access information quickly in a list or library.

Creating indexed columns

To improve the performance of a large list or library, you can index a column. An index on a column enables Microsoft Windows SharePoint Services 3.0 to quickly analyze the data in that column, even when working with thousands or millions of items. For a view to quickly filter through a large number of items, the filter must be applied to a column that is indexed.

It is important to consider the following when you create and use indexed columns:

• Each additional column index consumes extra resources in the database. Therefore, you should add indexes only to columns that will be used actively in views on the list or library.
• When you define a view in a list or library with lots of items, it is important to use a filter that will return no more than one thousand or two thousand items.
• Only one indexed column can be used in a view filter. You can filter on many different columns, but it is important that the first column that you use to filter the view has an index and that it sufficiently reduces the total number of items returned.
• Defining a view that uses an OR filter does not have any benefits of an indexed column.
• Defining an item limit in a view does not have the same benefits as filtering by an indexed column.

Creating filtered views

For lists and libraries with large numbers of items, users can access the items with a mix of views and search. The following are some suggestions for views that work well with an indexed column:

• Recently changed  To create a view of only the items that have changed in the past week, you can index the Modified column and then apply the filter Modified (Indexed) is greater than [Today]-7.
• New items  To create a view of only those items that were added in the past week, you can index the Created column and then apply the filter Created is greater than [Today]-7.
• My items  To create a view of only those items that you added, you can index the Created By column and then apply the filter Created By is equal to [Me].
• Due today  For lists or libraries with a Due Date column, you can index that column and then apply the filter Due Date is equal to [Today].
• Discussion board updates  To create a view of only the discussions that were updated in the past month, you can index the Last Updated column on a discussion board, create a new Subject view, and then apply the filter Last Updated is greater than [Today]-30.
• People and Groups  If you have a lot of people visiting your site, you can improve the performance of the All Groups and All People pages by indexing the Content Type column for the User Information List.

 Note    Even if only a few people appear on the All People page, many people visiting the site will result in inactive users being written to the User Information List, which can still affect the performance of this list.

Find links to more information about creating or changing views and about formulas and functions that you can use to filter views in the See Also section.

 Note    Because creating views that use a column index correctly is more complicated for large lists and libraries, you might want to remove the Manage Personal Views permission from contributors for a large list or library. By removing this permission, you can prevent users from creating a view that spans all of the items and that might adversely affect the performance of the rest of the site.

Organizing items into folders

Even when the total number of items in a list or library is very large, a view of a single folder is as fast as a view that filters the total number of items by using an indexed column. In some scenarios, it may be possible to distribute all of the items in a list or library into multiple folders such that no folder has more than one thousand or two thousand items.

It is important to consider the following when you use folders to organize a large list or library:

• Each folder must contain no more than one thousand or two thousand items to ensure fast views of the items in the folder.
• If you choose to display all items without folders when you create a view in this list or library, you must use a filter that is based on an indexed column.
• It is useful to make the default view show all the available folders without any filtering, so that users can choose the appropriate folder when they insert new items. This prevents items from being incorrectly added outside the folders in the list or library.