Blog

SharePoint - how to fix list threshold errors when working with very large lists

When we attempt to filter large SharePoint lists, the operation may fail with the error "The attempted operation is prohibited because it exceeds the list view threshold". This post describes this error, including how to fix this problem.

Although SharePoint can handle large quantities of data, querying large lists can be difficult.  Query delegation limitations cause the most problems, and list threshold values can also cause difficulties for app builders.

Demonstration of problem

To demonstrate the problem, let's look at an 'auto-generated' app that connects to a SharePoint list called clients. There are 40,000 records in this list.

We can immediately see the problem. The gallery control fails to show any data, and instead shows the error message -  "The attempted operation is prohibited because it exceeds the list view threshold"

What's the cause of this problem?

The cause of this problem is that the formula for the gallery control attempts to sort and to filter the list items by the title column. 

SortByColumns(Filter([@Client], 
                     StartsWith(Title, TextSearchBox1.Text)
               ), 
               "Title", 
               If(SortDescending1, Descending, Ascending)
)
If we set the items property of the gallery control directly to the client list, the error message disappears, and the gallery control displays the list data correctly.

What is a list view threshold?

What exactly is the "list view threshold" that the error message refers to?

The purpose of the list view threshold is to prevent users from querying data in a way that causes an excessive load on the server.

SharePoint stores its data in a SQL server database. When a user retrieves data from a SharePoint list through Power Apps, SharePoint queries SQL Server to service the request.

SQL Server relies on indexes to retrieve data in an optimal way. If there are inadequate indexes, SQL Server locks the underlying table whilst it executes the query. This causes a severe degradation in performance and concurrency - other users cannot access the data whilst there is a lock. The list view threshold is therefore designed to prevent this locking of data.

How can we filter/sort lists in Power Apps without this error?

We can prevent this error in Power Apps by defining indexes on the columns that we want to filter or to sort.

When we define an index in SharePoint, SharePoint creates a new table in SQL Server that stores the index value for each item in a list. This enables SQL Server to service queries that reference indexed values, without locking the entire list table.

To create an index, we navigate to the list settings page in SharePoint. As the screenshot beneath shows, the settings page indicates the number of items in the list, and the default list view threshold value (5,000).

Lower down this page is a link to the 'indexed columns' page. This opens the page shown beneath. If we index the columns that we require, the 'list view threshold' error will no longer appear in Power Apps.

Conclusion

In SharePoint, the list view threshold prevents excessive loads on the server that can cause a degradation in performance and concurrency.  We can fix errors that relate to 'list view thresholds' by defining appropriate indexes in SharePoint.
Previous
Next
Related posts