Manage lists and libraries with many items
No matter how big or small, lists and libraries are vital to you in many ways. But when a list or library has a large number of items, you need to carefully plan, organize, and account for how data is accessed. By planning and using key list and library features, you can ensure that you and other users quickly find information without adversely affecting the performance of the rest of your site.
In this article
Overview of lists and libraries with many items
You can work with very large lists and libraries. The following table summarizes the specifications and limits for lists and libraries that contain many items.
| Feature | Maximum Limit |
| Number of items in a list or library | 50 million |
| Size of an individual file item or item attachment | 2 Gigabytes |
As you can see, you can store a lot of data and files in lists and libraries. But if a query or database operation exceeds a resource throttle or limit, you may be blocked from doing this operation because accessing that many items could adversely affect other users of the site. Fortunately, there are a set of straightforward strategies and basic features you can use to achieve the results you want to prevent you from exceeding these throttles and limits. All users can circumvent the limit during a dedicated time window, and with appropriate permission you can also use a higher limit through code.
Why is 5,000 such a magic number?
To minimize database contention, SQL Server often uses row-level locking as a strategy to ensure accurate updates without adversely impacting other users who are accessing other rows. However, if a read or write database operation, such as a query, causes more than 5,000 rows to be locked at once, then it's more efficient for SQL Server to temporarily escalate the lock to the entire table until the database operation is completed. Note that the actual number is not always 5,000, and can vary depending on your site, the amount of activity in the database, and your site's configuration. When this lock escalation occurs, it prevents other users from accessing the table. If this happens too often, then users will experience a degradation of system performance. Therefore, thresholds and limits are essential to help minimize the impact of resource-intensive database operations and balance the needs of all users.
What happens behind the scenes when you access many items in a list or library?
The following diagram summarizes the key points about what happens behind the scenes when you access many items in a list or library.
1. List or library data in a site collection is stored in a SQL Server database table, which uses queries, indexes and locks to maintain overall performance, sharing, and accuracy.
2. Filtered views with column indexes (and other operations) create database queries that identify a subset of columns and rows and return this subset to your computer.
3. Thresholds and limits help throttle operations and balance resources for many simultaneous users.
4. Privileged developers can use object model overrides to temporarily increase thresholds and limits for custom applications.
5. Administrators can specify dedicated time windows for all users to do unlimited operations during off-peak hours.
6. Information workers can use appropriate views, styles, and page limits to speed up the display of data on the page.
Resource throttles and limits
The following table summarizes information about resource throttles and limits that you need to be aware of. These throttles and limits are set on the Resource Throttling page in Central Administration. Contact your administrator for specific limits and requests for your site.
Note To assist with management, the administrator is not subject to the following resource throttles and limits.
| Threshold | Default | Description |
| List View Threshold | 5,000 | Specifies the maximum number of list or library items that a database operation, such as a query, can process at one time. Operations that exceed this limit are blocked. To give you time to make alternative plans, You are warned on the List Settings page when your list has exceeded 3,000 items. The warning contains a help link to this topic. |
| Unique permissions limit | 50,000 | Specifies the maximum number of unique permissions allowed for a list or library. Every time you break the inheritance of permissions for an item or folder, it is counted as 1 unique permission toward this limit. If you try to add an item that would lead to exceeding this limit, you are prevented from doing so. |
| Row size limit | 6 | Specifies the maximum number of table rows internal to the database used for a list or library item. To accommodate wide lists with many columns, each item is wrapped over several internal table rows, up to 6 rows and up to a total of 8,000 bytes (excluding attachments). For example, if you have a list with many small columns, one that contains hundreds of Yes/No columns, then you could exceed this limit, in which case you would not be able to add more Yes/No columns to the list, but you still may be allowed to add columns of a different type. Note Administrators can only set this limit by using the object model, not through the user interface. |
| List View Lookup Threshold | 8 | Specifies the maximum number of join operations, such as those based on lookup, Person/Group, or workflow status columns. If the query uses more than eight columns, the operation is blocked. However, it is possible to programmatically select which columns to use by using maximal view, which can be set through the object model. |
| List View Threshold size for auditors and administrators | 20,000 | Specifies the maximum number of list or library items that a database operation, such as a query, can process at one time when performed by an auditor or administrator with appropriate permissions. This setting works in conjunction with Allow Object Model Override. |
| Allow Object Model Override | Y | Specifies whether or not developers can perform database operations, such as queries, that request an override of the List View Threshold to the higher limit specified by the List View Threshold for auditors and administrators. An administrator must enable an object model override, and then developers with appropriate permission may then programmatically request that their query use the higher List View Threshold to take advantage of it. |
| Daily time window | None | Specifies a time period during which resource thresholds and limits are ignored. An administrator can configure a time window during "off-peak" hours in 15 minute increments and up to 24 hours, such as, 6:00 PM to 10:00 PM or 1:30 AM to 5:15 AM. Note A database operation or query started within the daily time window continues until completion (or failure) even if it doesn't finish within the specified time window. |
Ways to manage lists and libraries with many items
The following sections provide recommendations, techniques, and tips for ensuring that you and other users can quickly access many items in a list or library and help to keep your site operating smoothly.
Creating SharePoint indexed columns
To help improve the performance of a large list or library, you can index up to 20 columns. In general, an index on a column enables you to quickly find the rows you want based on the values in that column, even when working with millions of items. When you combine indexes with filtered views, you can quickly retrieve the items you want.
It is important to consider the following when you create and use indexed columns. Each additional column index consumes extra resources in the database and adds some overhead to every operation to maintain the index. Therefore, you should add indexes only to columns that will be used actively for filtering in views on the list or library. It's a good idea to look at all the views and see which columns are used most commonly across different views for filtering to help choose the right columns to be indexed. Note that any column that you define to have a unique value requires an index.
The following table summarizes what columns can and cannot be indexed.
| Supported Column Types | Unsupported Column Types |
| Single line of text | Multiple lines of text |
| Choice (single value) | Choice (multi-valued) |
| Number | Calculated |
| Currency | Hyperlink or Picture |
| Date and Time | Custom Columns |
| Lookup (single value) Note Indexing the column in the other list or library does not improve performance of the lookup operation. | Lookup (multi-valued) |
| Person or Group (single value) | Person or Group (multi-valued) |
| Yes/No | External data |
| Managed Metadata |
For steps on how to create an indexed column, see the section Create a simple or compound index in this topic.
Creating filtered views based on column indexes
For a view to quickly filter through a large number of items, the first column that you specify in the filter must be indexed. Other columns you specify in the view filter may or may not be indexed, but the view does not use those indexes. You also need to make sure that the first column of the filter does not return more items than the List View Threshold, even if the final result of the filtered view returns less than the List View Threshold.
If the first column of the filter returns more items than the List View Threshold, you can use a filter with two or more columns. When you define a filtered view that uses two or more columns, using an AND operator will usually limit the total number of items returned. But even in this case, you still need to specify as the first column in the filter the column that most likely returns the lesser amount of data. Note that using an OR filter almost always increases the number of items returned and won't be effective under these circumstances.
Important Although you can index a lookup column to improve performance, using an indexed lookup column to prevent exceeding the List View Threshold does not work. Use another type of column as the primary or secondary index.
Important If you are filtering a list by an indexed field, check your recycle bin for deleted items. Items in the recycle bin aren't actually deleted from the backend database until they are removed from the recycle bin. If the total number of filtered items in the list and in the recycle bin is greater than the List Threshold limit, then you may get an incomplete set of results, or none at all.
The following are some suggestions for typical views that would work well with indexed columns:
| To filter by: | Index the: | For example: |
| Recently changed items | Modified column | To view only items that have changed in the past week, apply the filter Modified is greater than [Today]-7. |
| New items | Created column | To create a view of only those items that were added in the past week, apply the filter Created is greater than [Today]-7. |
| My items | Created By column | To create a view of only those items that you added, apply the filter Created By is equal to [Me]. |
| Items due today | Due Date column (created by you in a list or library) | To create a view of only those items with a due date of today, apply the filter Due Date is equal to [Today]. |
| Discussion board updates | Last Updated column | To create a view of only the discussions that were updated in the past month, apply the filter Last Updated is greater than [Today]-30. |
| Archive files in a document library | Date Modified | To create a view of documents not changed since the year 2006 (that you then want to archive), apply the filter Date Modified is less than December 31st 2006. |
| Find a subset of financial data | Region, Year (as two simple indexes) | To create a view of financial data for the NorthEast region in 2008, apply the filter Region equals "NE" AND Year equals 2008 because the Region column is likely to have less values than the Year column. |
Note Even when you create a filtered view based on column indexes, certain additional operations might still be blocked because they require accessing the entire list or library. These operations include the following: adding or deleting an index, creating a sort in the view definition; displaying a column total; and adding, updating, or deleting calculated fields.
Note If you are a developer, you can programmatically use a compound index on the first column. A compound index is based on two columns, primary and secondary, and can use either column to access the data quickly.
Organizing items into folders
Although folders (also called containers) are not required to use large lists and libraries, you can still use them to help organize your data and improve the efficiency of your data access. When you create a folder, behind the scenes you are creating an internal index. This internal index is also created for the root folder, or top-level of a list or library. When you access items in a folder, you are effectively using this internal index to access the data. Bear in mind that if a folder contains subfolders, each subfolder is counted as an item (but not any items in that subfolder).
Even when the total number of items in a list or library is very large, a view of a single folder is at least 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 five thousand items.
It is important to consider the following when you use folders to organize a large list or library:
-
A folder can contain more items than the List View Threshold, but to avoid being blocked, you may still need to use a filtered view based on column indexes.
-
If you choose the Show all items without folders option in the Folders section when you create or modify a view in this list or library, you must then use a filter that is based on a simple index to ensure you don't reach the List View Threshold.
-
It is often 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. Displaying all the folders also makes it less likely that items will be incorrectly added outside the folders in the list or library. Note that, unlike libraries, there is no automatic way to move items between folders in a list.
Controlling the display of data on the page
Once the subset of data has been transferred to your computer, there are additional strategies you can use to speed up the display of the data in your browser.
As you increase the size of the HTML source code behind a web page, there is usually a corresponding increase in time to render the page. The larger the page, the longer it takes to display. For example, the default style for a list or library view is the table style, which displays all the data, one item per table row, on one page. Therefore the more rows you display, the longer it takes to display the data.
Setting an item limit on a page
By setting an item limit on a page, you can directly control how many items display at one time by using a specific number, the default being 30.
There are two options you can choose when setting an item limit on a page:
-
If you select Display items in batches of the specified size (for paging), then you are creating a page-by-page display of the data which is useful when you are browsing items in a non-interactive way.
-
If you select Limit the total number of items returned to the specified amount, then you are creating a hard limit that may or may not return the entire results of your filter operation, but may be useful when testing your view, creating a prototype, or you only want to retrieve the top values in a view.
Using the Preview Pane view style
To see all of the information from items in your list in a vertical pane format, use the Preview Pane style. You can more quickly navigate the data by hovering over the item title in a scrolling region on the left side of the page, to see all the column values of the current item vertically listed on the right side of the page. Less initial data is displayed, and this helps display the view faster. This style is also very useful when your list is wide or has many columns and would require horizontal scrolling to see the data.
Using the Group By view style
The Group By view style groups the data by a category (up to two levels) that you choose when defining the view, which makes the initial display of the list view much smaller. You click a Plus/Minus button to expand/collapse a specific group of data and only load the subset of data that is needed onto the current page. This view style not only limits the display of the data, but also the retrieval of the data. Make sure that the default Show Grouping option is set to Collapsed. You can also control the paging of the grouped data in the same way as data that is not grouped. In some situations, such as simple reporting or drilling down and drilling up to find and update data, this view style can be much more efficient for users to use.
Using the daily time window
The daily time window is a way for administrators to specify a dedicated time period for all users to do unlimited operations during off-peak hours, such as 6:00 PM to 10:00 PM.
Although views are a primary way to retrieve items from a list or library, other SharePoint commands and operations also require accessing the items in a list or library, such as: adding and deleting an index, adding and modifying a list column, deleting and copying folders, changing security settings for a list or library, saving a list with its content as a template, deleting a web or site, and restoring or deleting items from the Recycle Bin. These commands and operation could also fail if they exceed the site thresholds and limits when retrieving list data.
If you are not a developer or don't have convenient access to developer resources to do these operations for you, you can defer these types of operations to the daily time window. If the regular daily time window period is set up during normal sleeping hours, you can ask the administrator to move the time period to an early morning or late evening hour.
Using offline synchronization
Taking data offline is often more convenient and efficient for you. You can make changes on your desktop or laptop computer, and then when you bring the data back online, synchronize changes and resolve conflicts in a smooth and efficient way. Working with large lists by using offline synchronization to "scrub", analyze, or report data, helps off-load database activity and minimize use of SharePoint resources.
You can take list data offline and synchronize changes when you come back online by using several Microsoft Office products.
Microsoft Access 2013 You can read and write most native lists from Microsoft Access 2013 by linking to them, and Access 2013 works well with virtually all SharePoint data types. Linking lets you connect to data in a SharePoint list, so that you create a two-way connection to view and edit the latest data both in the SharePoint list and your Access database. Access creates a copy of (or replicates) the SharePoint list in an Access table. Once the Access table has been created, you can work with list data in Access up to the Access limit of two gigabytes (excluding any attachments which are not stored locally). Furthermore, Access 2013, caches the list data on the client, uses an efficient in-memory, write-through cache, and only transfers changed list items, all of which makes queries and updates perform much faster. A conflict resolution dialog box also helps to manage conflicting updates in a smooth way.
If the Access Services feature is activated, then you can work with considerably more data than the List View Threshold, up to 50,000 items by default. Access 2013 automatically processes list or library data in small batches and then reassemble the data, a technique that enables working with substantially more data than the List View Threshold, and without adversely impacting other users on the SharePoint site. Find more information in the Using Access Services section.
Microsoft Excel 2013 You can export SharePoint lists to an Excel Table, which creates a one-way data connection between an the Excel table and the SharePoint list. When you update your data from the SharePoint list and refresh the Excel table, Excel 2013 replaces the Excel data with the latest SharePoint list data, overwriting any changes that you made to that Excel table. Once the data is in the Excel table, you can take advantage of the many data analysis features of Excel 2013, such as powerful and flexible worksheets, PivotTable reports, professional-looking charts and spark lines, conditional formatting with icons, data bars, and color scales, and sophisticated what-if analysis operations.
If the Access Services feature is activated, then you can work with considerably more data than the List View Threshold, up to 50,000 items by default. Find more information in the Using Access Services section.
Microsoft Outlook 2013 From Microsoft Outlook 2013, you can read and write Contact, Task, Calendar and Discussion Lists, as well as synchronize Document libraries. For example, you can work with both standard tasks lists and project tasks lists by taking task lists offline, viewing, updating, and assigning new tasks, bringing them back online, and synchronizing them without leaving Outlook 2013; You can also store, share, and manage SharePoint contacts more efficiently in Outlook 2013.
Using the Search box to find list or library items
An alternative way to find documents or items in a large library or list is to use the Search box to enter a keyword or phrase. SharePoint Search has its own indexing mechanisms, and it is not subject to the List View Threshold or other related limits. When you use the Search box, you can progressively expand the scope of the search operation:
-
By default, the search scope is initially based on all the items in the current view and any subfolders. You see the results as columns that you can further filter and sort. If the List View Threshold is currently exceeded, not all results are displayed.
-
If you don’t find what you are looking for, you can expand your search scope to include the entire list including all subfolders, regardless of the current view or List View Threshold.
-
Finally, you can expand the scope to search the entire site. In this case, you see all the results in the standard Search site page. You can further narrow the results by using the Refinement panel to filter, for example, by the author of a document or the creation date of a list item. You can even use Boolean syntax and logical operators to formulate more elaborate queries.
Note The Search box is only available for lists and libraries that are displayed as client-side rendered views. Although the default behavior is to display the Search box, the Search Box can be hidden by the List View Web Part property, Search Box, in the Miscellaneous section of the tool pane.
Using personal views, relational lists, and RSS feeds
To further help overall system performance and avoid reaching a resource threshold or limit, consider also the following commonly-used SharePoint features:
Personal views 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.
Relational lists When you create list relationships by using lookup columns, unique columns, and enforced relational behavior (also called referential integrity), you can reach the List View Threshold and may be blocked under the following circumstances:
-
If you make a column unique in an existing list that has more items than the List View Threshold (but note that adding one item to a list that makes the list exceed the List View Threshold is an operation that is not usually blocked).
-
If a list has more items than the List View Threshold, and then you turn on Cascade Delete or Restrict Delete) for a lookup field in that list.
RSS feeds After RSS support is enabled in Central Administration and at the site collection level, you can enable and configure RSS support for the many types of lists and libraries. When users access the RSS Feed for a list or library, data is retrieved from the list. The default RSS view limits the number of items that are returned, based on the date that the item was last modified by using a filter on the Modified column. If the list or library has many items and users access the RSS Feed, it's a good idea to index the Modified column. You can also reduce the number of items retrieved by changing the number of items and number of days for which changes are included in an RSS Feed.
Find more information about Managing RSS Feeds in the See Also section.
Using Access with SharePoint Products
In Access 2010, you create a Web database based on Access Services by building tables based on linked lists, along with queries, forms, reports, and macros, that you published to a SharePoint site. These Web databases are upgraded, you can still use and modify them, and you can create new ones from a template, but not from Access 2013.
The new approach is to create an Access App, which is a SharePoint App that you can create, and without using code. In general, a SharePoint App is a focused, point-solution that is easily distributed and made available in a marketplace. Think of an app as a convenient, alternative way to package, distribute, run, monitor, and retire a solution.
Unlike an Access Web database application that stores data in SharePoint lists, an Access App uses a SQL Server database separate from SharePoint Products to store data, and is therefore not subject to the SharePoint List View Threshold. This significantly improves the scalability of Access solutions and makes for efficient handling of potentially millions of records. For on-premise users, the SQL Server database can be configured to live behind your organization’s firewall. For online users, the database is an SQL Server Azure database enabled with a free account and free storage.
Finally, you can still synchronize data between a SharePoint list and an Access table, and keep both sets of data up-to-date. This synchronization is two-way or bi-directional. Changes made in Access 2013 to the list data are uploaded to the SharePoint list, and changes made to the SharePoint list are downloaded to an Access on your computer. When you synchronize data, behind the scenes you are using Access Services, which lets you work with considerably more data than the List View Threshold without being blocked. Access Services automatically processes data in batches of 2000 items at a time and then reassembles the data. The default limit is 50,000 items although this can be changed by an administrator. Note that Access Services has its own set of limits and thresholds.
Using indexed and fallback queries
Metadata Navigation and Filtering is enabled by default on most SharePoint sites. Even if metadata navigation has not been configured for a specific list or library, Metadata Navigation and Filtering is still at work behind the scenes to improve the performance of views in lists and libraries. The Metadata Navigation and Filtering feature can automatically select the best index to work every time a view is loaded. When you load new views, apply filters to views, clear filters, or apply a sort on a field, query optimization determines the best way in which to query the database.
If a user creates or loads a view that cannot use an index to query the list, then Metadata Navigation and Filtering will construct and execute a fallback query. A fallback query is a modified version of the original user query that displays a partial set of the items requested because it queries against only a portion of the list instead of the entire list. It is intended to provide you with some useful results in circumstances when the original query is blocked due to large list throttling. Up to 1,250 of the newest items are displayed based on when those list items were added to the list. Occasionally, fallback queries will return 0 results if no items in the part of the list scanned by the query contain results that match the original user query.
Using the Document Center site
Note In Office 365, the Document Center site is not available in Office 365 Business Essentials and Office 365 Business Premium. It is also not available in Office 365 Small Business and Office 365 Small Business Premium, which are no longer available for purchase.
You can use a Document Center site when you want to create, manage, and store large numbers of documents. A Document Center is based on a site template and is designed to serve as a centralized repository for managing many documents. Features, such as metadata and tree view navigation, content types, and web parts, help you organize and retrieve documents in an efficient and meaningful way for your users.
Content Stewards can quickly configure metadata-driven navigation to perform well for most libraries without explicitly creating indexes, but also get assisted when creating additional indexes to enhance the performance over a wider range of filters and views.
You can use a Document Center site as an authoring environment or a content archive:
-
In an authoring environment, users actively check files in and out and create folder structures for those files. Versioning is enabled, and 10 or more earlier versions of each document can exist. Users check documents in and out frequently, and workflows can help automate actions on the documents.
-
In a content (or knowledge-base) archive, by contrast, very little authoring occurs. Users only view or upload documents. Typically, content archives contain single versions of documents, and a site can scale to millions of files. In a typical scenario, such as a technical support center for a large organization, 10,000 users might access the content, primarily to read it. A subset of 3,000 to 4,000 users might upload new content to the site.
Using external data
Note For Microsoft Office 365, using external data features is only supported in the following editions: E3, E4, A3, A4, G3, and G4.
You can integrate external data into SharePoint products by using Business Connectivity Services (BCS), which provides standardized interfaces to external data sources, such as Line-of-Business (LOB) databases, Enterprise Resource Planning (ERP) systems, OData, and Web services, depending on which SharePoint product you are using. This external data is often made available through external lists. An external list behaves in many respects like a native list. For example, you can create, read, update, and delete data, and create views.
An external list is not subject to the same resource throttles and limits as a native list or library, although it is subject to BCS throttles and limits. In some cases, you may find that an external list is more practical alternative for your data and application requirements.
How to index and filter views
The following sections provide step-by-step procedures for creating indexes and using these indexes in filtered views.
Create a simple or compound index
To improve the performance of a large list or library, you can create an index. Then you can use the indexed column when you Create or modify a filtered view based on column indexes of that list or library.
Important Because creating an index requires accessing all items in the list, it's possible you may be blocked from creating an index for a column if the entire list exceeds the List View Threshold. If this occurs, do the operation during the daily time window, or contact your administrator.
Important Although you can index a lookup column to improve performance, using an indexed lookup column to prevent exceeding the List View Threshold does not work. Use another type of column as the primary or secondary index.
-
Navigate to the site that contains the list or library.
-
Click its name on the Quick Launch, or click on the Settings menu
, click View All Site Content, and then click the name. -
Click List or Library > List Settings or Library Settings.
-
Under the Columns section, make note of the type for each column. Make sure you do not select a Lookup column in step 7.
-
Click Indexed columns.
-
On the Indexed Columns page, click Create a new index.
-
Do the following:
-
To create a simple index, in the Primary Column section, under Primary column for this index, select a column that is not a Lookup column.
-
To create a compound index, in the Secondary Column section, under Secondary column for this index, select a different column that is not a Lookup column.
-
Click Create.
-
Create or modify a filtered view based on column indexes
After you index a column, you can add it to a view when you create or modify a view, and then use the indexed column to filter the view. Before creating a view, you may want to add more columns to the list to enable more flexibility for sorting, grouping, and filtering.
Find more information about creating columns and views in the See Also section.
-
Navigate to the site that contains the list or library.
-
Click its name on the Quick Launch, or click on the Settings menu
, click View All Site Content, and then click the name. -
In the ribbon, under List Tools or Library Tools, click the List or Library tab.
-
Do one of the following:
To create a view:
-
In the Managed Views group, click Create View.
-
Click the option, such as Standard View or an existing view, that you want to use to create your view.
-
In the View Name box, type the name for your view.
Select Make this the default view if you want to make this the default view for the list or library. Only a public view can be the default view for a list or library.
To modify a view:
-
In the Managed Views group, select the view you want to modify from the Current View drop-down list.
-
In the Managed Views group, click Modify this View.
-
-
In the Filter section, click Show items only when the following is true, and then choose how you want to filter the items based on one of the columns that is indexed.
For example, to create a view of only items that changed today, choose the Modified (Indexed) column and the is equal to condition, and then type [Today].
Note If you do not see any columns that say (Indexed) after the name, you do not have any indexed columns available for that list or library. You must first Create a simple or compound index.
-
Choose any other options that you want for your view, such as which columns you want to show or hide and how you want to sort the items.
-
Click OK.
Find more information about creating views in the See Also section.
3883
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
