List View Threshold on List vs Web Part

You may find the following links helpful:

• https://support.office.com/en-us/article/Manage-large-lists-and-libraries-in-SharePoint-b8588dae-9387-48c2-9248-c24122f07c59?ui=en-US&rs=en-US&ad=US (support.office.com)
• https://en.share-gate.com/blog/demystifying-the-sharepoint-list-thresholds (Sharegate blog)
• http://sympmarc.com/ - many posts expressing frustrations and workarounds
• http://www.learningsharepoint.com/2013/12/16/things-to-note-about-indexed-columns-in-sharepoint/ (about 2010 - but still applicable to 2013, 2016 and O365. I've found it among the best simple overviews with a great checklist)

Ran into this 'blindly' uploading a large list without having established indices and views in advance. Once the view threshold's exceeded, I found it nigh impossible to fix afterword: creating indices and views filtered by those indices (very important) ran into same brick walls. Tried deleting below 5,000 - again ran into resource throttling errors and discovered along the way that deleted items must be removed from the recycle bin to free up throttling. And deleting items in and of itself faces throttling. Retrieving large batches of data, deleting - very expensive SQL operations. Why they're throttled.

My fix? Access (yes Access). I've been using Access & SharePoint together for years. For example, SharePoint lists have no reporting capabilities - Access has sophisticated reporting features. In this case:

1. created an offline copy of the large list in Access
2. deleted the SharePoint list items from Access
3. ensured they were emptied from SharePoint's recycle bin
4. to the now empty list, added my indices, metadata navigation (creates indices automatically if using this feature) & filtered views (at least the initial views I knew I wanted)
5. reloaded the list data via an Access append query

Worked like a charm. Access interacts with SharePoint lists via SQL commands. In the SQL Server world, SQL queries are automatically optimized into query plans before running against databases. Microsoft also built in caching and batching mechanisms between Access & SharePoint lists.

IMPORTANT: despite common sense and a plain reading of the Access option description, make sure you uncheck "Use the cache format that is compatible with Microsoft Access 2010 and later" (File -> Options -> Current Database -> Caching Web Service and SharePoint tables). Otherwise these operations will fail sometimes. This clears it up.

HELPFUL: after a few sessions of data-jockeying, found that clearing the Access/SharePoint caches greatly improves performance and clears up any glitches. https://blogs.msdn.microsoft.com/varun_malhotra/2012/01/28/sharepoint-2010-clear-sharepoint-cache/ for a nice how-to. Old post, update folder locations to

• %APPDATA%\Microsoft\Web Server Extensions\Cache
• %USERPROFILE%\AppData\Local\Microsoft\WebsiteCache

Hope this helps