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.
jennratten Thanks for the information on indexing. I've already indexed all the relevant fields which is why most of the filters work. I suspect the issue is some limitation with the indexing when a large number of records share the same index value. In my case the Title field is the same value for more than two-thirds of the records and using that as a filter fails. If I choose a field value with fewer matches the filter works, and if I combine the failing filter with a filter on another field that also works. Unfortunately I can't find any information on what the limitation is based on: is is the relative differentiation of the indexed field, or is it the number of matches for a particular index value?
As so often, while the features of the software are documented in great detail, and marketed at length, the limitations of the software are poorly documented or unacknowledged. It's unfortunate that these are the things that disproportionately soak up development time.
Thanks for your help in trying to track down the issue. For now I will use my workaround of breaking down the data into smaller parts and recombining them into the whole.
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.