SharePoint List View Threshold Error while Fetching 5000+ Records in list.

Question

I am fetching data from a SharePoint list that contains more than 5000 records inside my function. While retrieving the data, I am getting the following error:

“Flow Reminders→ The attempted operation is prohibited because it exceeds the list view threshold.”

 

Scenario:

• The SharePoint list has 5000+ items.
• Data is being fetched programmatically.
• The error occurs during query execution. What I need help with What is the recommended and best-practice approach to retrieve large datasets from a SharePoint list without hitting the list view threshold? Questions:

1. What is the recommended approach to fetch large data from a SharePoint list without hitting the list view threshold? Any suggestions, examples, or documentation references would be greatly appreciated.

Thanks.

Answer 1

Hi Ajay Mahajan,

Thank you for posting your question in the Microsoft Q&A forum.

Based on my research, Microsoft has a dedicated article for SharePoint Server titled Working with the List View Threshold limit for SharePoint Server only, which outlines multiple approaches you can consider (for example, using a Daily Time Window and noting that an administrator can choose to raise the limit in SharePoint Server). However, Microsoft also explicitly advises against changing query throttling thresholds, because the default threshold is tied to SQL Server locking behavior and raising it can negatively affect overall throughput. So, increasing the limit should be treated only as a temporary stopgap or used in very highly controlled environments, rather than as a best-practice fix.

Below are a few workarounds I’ve seen others recommend that you may want to apply by rewriting your function to handle large datasets more gracefully:

1) Indexed Columns

If your query involves filtering (for example, using $filter), the column being filtered should be indexed in List Settings so SharePoint can execute the query efficiently. If a column is not indexed, SharePoint has to scan the entire list to find matches, immediately hitting the 5000-item limit.

2) Implement Pagination

Use pagination to fetch data in batches (for example, $top=5000 or a lower number like $top=1000). In REST responses (commonly with application/json;odata=verbose), users often handle paging by following the data.d.__next URL until it is no longer returned, which indicates there are no more pages.

Best practice implementation pattern: loop (or recurse) and continue calling the returned __next URL until it becomes null / absent.

3) Server-Side Filtering & Selection

• $filter: Apply filters so the dataset returned per batch is as small as possible (reduce the workload of each query).
• $select: Request only the specific columns needed by your function to reduce payload size and improve execution time.

Reference: https://support.microsoft.com/en-us/office/overview-of-large-lists-and-libraries-e2ea4d5d-ec23-4171-95c4-c7f5b5dbfd8a

I hope the above information is helpful.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".         

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

To retrieve large datasets from a SharePoint list without hitting the list view threshold of 5,000 items, you can follow these best practices:

1. Use Indexed Columns: Create indexed columns on the fields you frequently filter or sort by. This allows you to create views that return fewer than 5,000 items. When querying, ensure that your filters utilize these indexed columns.
2. Create Filtered Views: Design views that limit the number of items returned to 5,000 or fewer. You can do this by applying filters based on indexed columns, which helps in managing the data efficiently.
3. Paginate Results: If you're fetching data programmatically, implement pagination in your queries. This means retrieving data in smaller chunks rather than all at once, which helps avoid exceeding the threshold.
4. Utilize Folders: Organize your items into folders within the list or library. This can help keep the number of items displayed in a view to under 5,000.
5. Use REST API or CSOM: When fetching data programmatically, consider using the SharePoint REST API or Client-Side Object Model (CSOM) to handle large datasets more effectively. Both methods allow for more control over the data retrieval process.
6. Consider List Views: If you are using SharePoint Online, be aware that certain operations may be throttled. Ensure that your queries are optimized to avoid running into performance issues.

For more detailed guidance, you can refer to the documentation on managing large lists and libraries in SharePoint.

---

References:

• "The number of items in this list exceeds the list view threshold" when you view lists in Microsoft 365
• "Cannot show the value of the filter" error when you try to sort or filter a column in SharePoint Online