Recommendations for updating List items with data from any data source

General background: SharePoint Architecture for List Item Update
To programmatically update Items in SharePoint Lists and Libraries the only way SharePoint allows that is by specifying the unique ID for each Item. This is the design SharePoint has.

The 3 generic steps necessary to perform any SharePoint Item Update. (Not solution specific)
  1. From the List to update, download the data from the SharePoint Item’s ID and key columns you want to match. Speed primarily depends on SharePoint
  2. Resolve the SharePoint Item ID’s using key data from the source data set. Speed depends on the CPU core performance of the local machine
  3. Perform the SharePoint Item update. Speed depends on SharePoint and the level of multitasking used.

Recommendations for updating Items on large SharePoint Lists (Item count >150.000)

  • Use incremental design and only update as few items as possible.
  • Local machine: Get as much local CPU Power as possible. Individual CPU core speed counts – not the number of cores. (Specific to step 2 )
  • Local machine: Get at least 12 GB memory.
  • Limit the Source data set to approximately 150.000 rows.
  • Limit the Target set of SharePoint Items to approximately 200.000 Items by specifying a “Server Query” – the equivalent of the SQL WHERE clause. Use Indexed columns in the query and operators that can use the indexes: < , > , <> , = , BeginsWith. (Step 1)
  • Know that resolving SharePoint Lookups on the Target SharePoint List can be time consuming. This is done on the local machine. (Part of step 2)

Need more help with this?

Thanks for your feedback.