Performance impact and throttling risks of using $orderby=FileRef desc on large document libraries

Using $orderby=FileRef desc on large document libraries in SharePoint Online can have significant implications regarding resource consumption, latency, and throttling risks.

Resource Consumption

Sorting by FileRef instead of the default ID may indeed consume more Resource Units (RUs) per call. The operation could require a non-clustered index seek, which is generally more resource-intensive than a clustered index scan that would occur with ID-based sorting. This increased resource consumption could lead to depleting your throttling quota more quickly, especially under heavy load or frequent queries.

Latency

Regarding server-side latency, adding this sort order can introduce measurable delays. The SharePoint engine must process the sort order before it can stream the data, which could affect the Time-to-First-Byte (TTFB) for the first page of results. This is particularly relevant in large libraries, where the overhead of sorting can be more pronounced.

Throttling Risks

Using this specific sort operation is likely to be considered a more complex query, which increases the risk of triggering HTTP 429 errors or hitting the List View Threshold (5,000 item limit). Complex queries can lead to throttling, especially if they are executed frequently or if they are part of a larger pattern of high-volume requests.

Given these factors, relying on this sort order in large-scale ingestion scenarios may be considered an anti-pattern. It would be advisable to evaluate your architecture and consider alternative approaches that minimize resource consumption and latency while avoiding throttling risks.

References:

• Avoid getting throttled or blocked in SharePoint Online

Hi DSR Automation, 

Welcome to Microsoft Q&A Forum! 

Have a good day and i hope you're doing well! 

I completely understand and empathize with your need to process folders in a bottom-up manner (from child files/folders up to parent) for tasks like deletion, moving, or inheritance logic. This is a very common and valid requirement in large migration/sync projects. 

$orderby=FileRef desc is technically allowed; REST API supports it and it’s not blocked. However, for very large libraries, it can lead to higher latency and increased throttling risk compared to other approaches. 

Based on my research, including community discussions and real-world measurements across large tenants, the current trend is that Microsoft and major migration tools tend to avoid or no longer use this method for libraries over 50,000–100,000 items because: 

1. Significant resource consumption: Sorting by FileRef generally costs more than default ID-based paging. Some observed measurements indicate resource usage can be several times higher than ID-based queries. 

2. Quota risk: For libraries with hundreds of thousands of items, a full scan using FileRef sorting can consume substantial resources, potentially impacting other jobs (Power Automate, Graph, Power BI, etc.). 

3. Increased latency: Time-to-First-Byte can be noticeably higher compared to ID-based sorting, especially when the entire dataset needs to be processed before returning the first page. 

(Note: Detailed RU numbers are not officially published by Microsoft; these insights are based on field experience and community feedback.) 

If you still need bottom-up logic, here are three practical options that have proven stable for large libraries: 

1. Most efficient & cost-effective:  Use Microsoft Graph API with default ID-based paging > build the folder tree in your code > process bottom-up. 

References: List children of a driveItem 

2. If server-side sorting by path is required: Use SharePoint Search API with sort=Path:descending. 

References:

• Sorting search results in SharePoint 
• Search REST API overview 

3. Classic but still reliable:  REST + $skiptoken with ID-based paging > handle folder hierarchy in memory (PnP PowerShell currently uses this approach). 

References: Track changes with delta query (Microsoft Graph) 

Please understand that as a forum moderator, my role is to assist based on public documentation, research, and community experience.  

I hope this helps or clarifies the performance and throttling risks when using $orderby=FileRef desc on very large libraries. If you have any concerns or questions, or if I misunderstood or left anything unclear, please feel free to reach out. 

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.