Scale, large lists and multi-site consolidation
Working past SharePoint's list-view threshold and bringing the same list from many sites together into one reportable table.
A list that started as a simple tracker is now well past 5,000 items. In the browser nothing looks wrong, because the list view still displays, filters and sorts.
The trouble shows up in automation. A Power Automate flow or a custom app that filters or sorts on a column without an index can fail once the list passes the 5,000-item list view threshold. Microsoft Graph refuses such a query unless the caller opts in, and warns that it may fail on a large list even then. Indexing a column helps a simple filter on that column. A report that needs totals across the whole list, or a join to a second list, still has to page through every item.
A database copy moves that work into a SQL engine. SPSync reads the whole list once on the first sync, through Graph's delta and paging endpoints and never through a filtered view, so the threshold does not apply to the sync itself. Every later run reads only the items that changed. Filtering, sorting and totals then run against whatever indexes you add to the table. The largest runs so far are 10,000 items on a live tenant and 1,000,000 on a synthetic Graph, and the first full load of a big list takes longer than the deltas that follow it.
The other scale problem is breadth. A project office with the same risk register on every project site ends up with one report per site. One SPSync replication can name all of those sites and write them to one database, with two ways to lay out the tables. By default each site gets its own table prefix, and the report is a UNION ALL view over the per-site tables. The other route merges lists that share a name into one table, where every row carries an sp_site_id column and SPSync adds that column to the primary key, because item 14 exists on every site. Pick the merged table when the sites were built from one template, and a prefix per site when their lists have drifted apart or each site's rows need a table of their own. spsync batch create builds the replication from one batch file that lists the sites, so adding the next site is one more entry in that file.
SPSync does not raise the threshold. Microsoft sets it, and it still applies to the SharePoint UI and to anything else that queries the list directly. Throttling applies to SPSync as well. Every request respects the tenant's budget, and sites replicated from the same tenant share it.
Start here
- What breaks past 5,000 items and how delta reading avoids it
The threshold comes first, with what breaks past 5,000 items and why delta reading is not affected.
- Combine SharePoint lists from multiple sites into one table
The multi-site case builds on it, with a prefix per site or one merged table keyed by sp_site_id, and one query across all of them.
All posts on this topic
- Graph list item delta: paging, tokens and missed deletes
You built a sync on /items/delta, deleted an item, and the next call never said so. How listItem delta pages, what its tokens carry, and the reconcile that catches the rest.
- The 12 lookup column threshold in views, flows, Power Apps and Power BI
Add a thirteenth lookup or person column and views, flows, apps and Power BI refreshes start failing with one error. What counts, where it shows, and a fix.
- Combine SharePoint lists from multiple sites into one table
Twelve project sites, one RAID log template, and a portfolio report built by hand every Monday. How a per-site prefix or one merged table puts every site's list in SQL.
- What breaks past 5,000 items and how delta reading avoids it
A service desk list passes 5,000 items and its views, flows and exports stop. What the list view threshold blocks, where indexes stop helping, and how a delta read copies the whole list.
Other topics on the blog
Frequently asked questions
Will it work on our largest lists?
SPSync has been tested to 10,000 items on a live tenant and to 1,000,000 on a synthetic Graph. Lists are read by delta query and paged the way Microsoft recommends, so the 5,000-item view threshold does not apply, and every request respects the tenant's throttling.
Does the sync itself trip the threshold?
It does not. SPSync reads through the Graph API's delta and paging endpoints, never a filtered list view, so the 5,000-item threshold does not apply to it as it does to a browser or a Power Automate flow.
How does SPSync tell which site a row came from?
Each site's tables get their own prefix in the destination database, so rows stay distinguishable by source even when every site uses the same template. A view then unions the per-site tables.