Forum Discussion
SharePoint list view threshold error when importing fewer than 5000 rows
Here are a couple of tidbits that may (or may not) be helpful...
If you move items into the Recycle Bin, those items will still be counted when determining whether the filter expression exceeds the List View Threshold. If you clear the recycle bin they are no longer counted.
Automatic Index Creation
By default, SharePoint performs automatic index creation. When automatic index creation is enabled, indexes are created automatically for the fields that you add to the navigation tree and Key Filters. Single column indexes are created on all supported Key Filter fields except for the Content Type and Choice fields. Compound indexes are created on all supported combinations of navigation hierarchies and Key Filters.
When indexes are created automatically, queries are enabled for lists that have more items than the list view threshold. However, in some cases, you might have to disable this setting and set up indexes manually. For example, if the combination of single column and compound indices exceeds 20 (the maximum number of indexes per list), you must disable automatic indexing and configure the indexes manually.
Important: Although you can index a lookup column to improve performance, using an indexed lookup column to prevent exceeding the List View Threshold doesn't work. Use another type of column as the primary or secondary index.
If you are adding an index to avoid a List View Threshold error, ensure that the field used is not a lookup field. Please see supported column to determine which field types are lookup fields.
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. 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.
Here are a couple of tidbits that may (or may not) be helpful...
If you move items into the Recycle Bin, those items will still be counted when determining whether the filter expression exceeds the List View Threshold. If you clear the recycle bin they are no longer counted.
Automatic Index Creation
By default, SharePoint performs automatic index creation. When automatic index creation is enabled, indexes are created automatically for the fields that you add to the navigation tree and Key Filters. Single column indexes are created on all supported Key Filter fields except for the Content Type and Choice fields. Compound indexes are created on all supported combinations of navigation hierarchies and Key Filters.
When indexes are created automatically, queries are enabled for lists that have more items than the list view threshold. However, in some cases, you might have to disable this setting and set up indexes manually. For example, if the combination of single column and compound indices exceeds 20 (the maximum number of indexes per list), you must disable automatic indexing and configure the indexes manually.
Important: Although you can index a lookup column to improve performance, using an indexed lookup column to prevent exceeding the List View Threshold doesn't work. Use another type of column as the primary or secondary index.
If you are adding an index to avoid a List View Threshold error, ensure that the field used is not a lookup field. Please see supported column to determine which field types are lookup fields.
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. 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.
jennratten Thanks for the additional information. The thing that might possiblty explain the issue is the Recycle Bin one: I did delete some records but, as I can't see a way to count them in the Recycle Bin, it's hard to know their impact.
Thanks for all your help in my understanding this area better.