cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
biterbit
Frequent Visitor

SharePoint list view threshold error when importing fewer than 5000 rows

I have a Power BI report that is based on a set of SharePoint Online lists. I recently hit a problem when one of the lists went above 5000 items and the refresh failed. After looking up ideas online I rewrote the query to use an OData connection rather than the standard SharePoint connector and this seemed to work. However, after a few days of working, the refresh has failed again on the same table. I now get the following error:

DataSource.Error: Microsoft.Mashup.Engine1.Library.Resources.HttpResource: Request failed: 
OData Version: 3 and 4, Error: The remote server returned an error: (500) Internal Server Error. (The attempted operation is prohibited because it exceeds the list view threshold.)
OData Version: 4, Error: The remote server returned an error: (500) Internal Server Error. (The attempted operation is prohibited because it exceeds the list view threshold.)
OData Version: 3, Error: The remote server returned an error: (500) Internal Server Error. (The attempted operation is prohibited because it exceeds the list view threshold.)
Details:
    DataSourceKind=OData
    DataSourcePath=https://<MYSITE>/_api/web/lists/getByTitle('Statistics')/items
    SPRequestGuid=042414a0-7022-3000-90d2-e30508739d90, 052314a0-5057-3000-90d2-e35ecaffdb42, 042314a0-402b-3000-91d2-e4f5bc8dfae8

I checked the SharePoint list and it has 5117 items. I have several queries which extract different types of data from this list based on the values in the Title or GroupBy1 columns (both of which are indexed):

= OData.Feed("<MYSITE>" & "_api/web/lists/getByTitle('Statistics')/items?" & "$select=ID, GroupBy2, Date, Value" & "&$filter= GroupBy1 eq 'Enquiry'", null, [Implementation="2.0"])
= OData.Feed("<MYSITE>" & "_api/web/lists/getByTitle('Statistics')/items?" & "$select=Date, GroupBy1, Value" & "&$filter= Title eq 'Potential counselling income'", null, [Implementation="2.0"])
= OData.Feed("<MYSITE>" & "_api/web/lists/getByTitle('Statistics')/items?" & "$select=Date, GroupBy1, GroupBy2, Value" & "&$filter= Title eq 'Clients by status'", null, [Implementation="2.0"])

Although all three queries are similar in design, only the third one fails. The only difference I can find is that the third query should return 3858 rows; the other queries all return fewer rows. If I add more filters to the third query to reduce the number of rows, it works.

I understand the 5000 rows limit when using SharePoint views but I don't understand what other limit I seem to be hitting here. Does anyone know why the query returning 3858 rows fails?

1 ACCEPTED SOLUTION

Here are a couple of tidbits that may (or may not) be helpful...

 

https://support.microsoft.com/en-us/office/use-filtering-to-modify-a-sharepoint-view-3d8efc52-0808-4... 

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. 

 

https://support.microsoft.com/en-us/office/set-up-metadata-navigation-for-a-list-or-library-c222a75d... 

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.

View solution in original post

6 REPLIES 6
biterbit
Frequent Visitor

@jennratten Thanks for the links. I think one of them relates to a different connector and the second is the one I used to work around the 5000 record limit using OData, which has led me to the second issue. In the meantime I have bypassed the problem by adding filters to my failing query to reduce the number of records to under 3000 and then appending several slightly different queries together to reproduce the original one.

This may help also - there are a few webpages and articles that state adding an index column to the Sharepoint list will bypass the 5000 limit in some scenarios.

 

https://techcommunity.microsoft.com/t5/sharepoint/searching-for-items-in-sharepoint-lists-with-over-... 

https://support.microsoft.com/en-us/office/manage-large-lists-and-libraries-b8588dae-9387-48c2-9248-... 

@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...

 

https://support.microsoft.com/en-us/office/use-filtering-to-modify-a-sharepoint-view-3d8efc52-0808-4... 

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. 

 

https://support.microsoft.com/en-us/office/set-up-metadata-navigation-for-a-list-or-library-c222a75d... 

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.

jennratten
Super User
Super User

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May 23 2022 episode 5 with aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt will talk about the importance of Data Modeling.

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!