Resolving the List View Threshold when Migrating to O365

[RHR 04/27/2015]: We added this indexing capability to a recent release of MNSP. So you don't need to run this script anymore for Notes application migrations using MNSP.

 

We recently had a support issue from a customer on migrating 5000+ items to a document list on O365. The customer reported an error with the following details:

Message:    The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator.

Stack trace: Microsoft.SharePoint.Client.ServerException: The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator.

In on-premises SharePoint 2010/2013, we can change the list view threshold to 5000+ to avoid this error without any code changes. But in Office 365, the customer don't have this permission.

Konstantin, the Lead Software Architect for NMSP, determined that we could bypass the list view threshold by indexing the NotesUNID column. He referenced the following details:

Query Throttling and Indexing

https://msdn.microsoft.com/en-us/library/ff798465.aspx

What is Query Throttling?

Query throttling is a new administrative feature in SharePoint 2010. It allows farm administrators to mitigate the performance issues associated with large lists by restricting the number of items that can be accessed when you execute a query (known as the list view threshold).

List-based throttling applies to other operations as well as read operations. In addition to query operations, throttling also applies to the following scenarios:

  • Deleting a list or folder that contains more than 5,000 items
  • Deleting a site that contains more than 5,000 items in total across the site
  • Creating an index on a list that contains more than 5,000 items

How Does Indexing Affect Throttling?

The list view threshold does not apply simply to the number of results returned by your query. Instead, it restricts the numbers of database rows that can be accessed in order to complete execution of the query at the row level in the content database. For example, suppose you are working with a list that contains 10,000 items. If you were to build a query that returns the first 100 items sorted by the ID field, the query would execute without issue because the ID column is always indexed. However, if you were to build a query that returns the first 100 items sorted by a non-indexed Title field, the query would have to scan all 10,000 rows in the content database in order to determine the sort order by title before returning the first 100 items. Because of this, the query would be throttled, and rightly so—this is a resource-intensive operation.

In this case, you could avoid the issue by indexing the Title field. This would enable SharePoint to determine the top 100 items sorted by title from the index without scanning all 10,000 list items in the database. The same concepts that apply to sort operations also apply to where clauses and join predicates in list queries. Careful use of column indexing can mitigate many large list performance issues and help you to avoid query throttling limits.

 

We use the NotesUNID field as the ID field in lists. The NotesUNID field is the unique identifier for Notes documents.

You can set NotesUNID as an indexed column in Settings \ Indexed Columns:

PowerShell Script

Below is a PowerShell script to add an index to an existing list. This script works only if the list has less than 5000 items. You will need to delete items until you get below 5000 in order to run the script. 

Please update the parameter values before running the script.

Note: You do not need to include the line numbers in the script.

  1. $listTitle = "IndexedNotesUNID"
  2. $indexField = "NotesUNID"
  3. $username = "YOUR USERNAME"
  4. $password = "YOUR PASSWORD"
  5. $siteUrl = "https://questsoftware.sharepoint.com/sites/nmspFitnesse"
  6.   
  7.   
  8. Set-Location "C:\Program Files (x86)\Dell\Migrator for Notes to SharePoint\Bin"
  9. Add-Type -Path (Resolve-Path "Microsoft.SharePoint.Client.dll")
  10. Add-Type -Path (Resolve-Path "Microsoft.SharePoint.Client.Runtime.dll")
  11.   
  12.   
  13. $secstr = New-Object -TypeName System.Security.SecureString
  14. $password.ToCharArray() | ForEach-Object {$secstr.AppendChar($_)}
  15. $AdminCred = new-object Microsoft.SharePoint.Client.SharePointOnlineCredentials($username, $secstr)
  16.   
  17.   
  18. $ctx = New-Object Microsoft.SharePoint.Client.ClientContext($siteUrl)
  19. $ctx.Credentials = $AdminCred
  20.   
  21.   
  22. $list = $ctx.Site.RootWeb.Lists.GetByTitle($listTitle)
  23. $field = $list.Fields.GetByInternalNameOrTitle($indexField)
  24. $field.indexed = $true
  25. $field.Update()
  26. $ctx.Load($field)
  27. $ctx.ExecuteQuery()

Our customer is now able to bypass the list view threshold limit when migrating to O365.

You can follow Konstantin on Twitter at @velaskec. His blog can be found at https://vlasenko.org/

Randy Rempel

Senior Product Manager

Anonymous