How can I speed-up list item migration between two Sharepoint sites?

Question

I have two Sharepoint sites, let's call them Business and Party for example.

The Business site has a list with 1000+ items. I want to copy the list items to the Party site. I want to do this in a repeatable, automated way.

I found that I can use PnP Powershell in a Powershell script:

# Get credentials from the windows credentials manager
$creds = Get-StoredCredential -Target 'my.company.url'

# Define site and list name
$origin_url = "https://company.sharepoint.com/site/business"
$origin_list = "Listname"
$destination_url = "https://company.sharepoint.com/site/party"
$destination_list = "Listname"

# Connect to the origin site
$o = Connect-PnPOnline –Url $origin_url -Credentials $creds -ReturnConnection

# Connect to the destination site
$d = Connect-PnPOnline –Url $destination_url -Credentials $creds -ReturnConnection

# Get all items in the origin list
$all = Get-PnPListItem -List $origin_list -Connection $o

# Loop over all items in origin
# Get all required information for the associated ID
# Add it to the destination list
Write-Host "--- Copying items ---"
 foreach($id in $all) {
    $term = $id.FieldValues
    $itemValues = @{  
        "Columnname1" = $term["Columnname1"];
        "Columnname2" = $term["Columnname2"];
        "Columnname3" = $term["Columnname3"];
        "Columnname4" = $term["Columnname4"];
    }
    Add-PnPListItem -List $destination_list -Values $itemValues -Connection $d
}

# Close connection to sites
Disconnect-PnPOnline -Connection $o
Disconnect-PnPOnline -Connection $d

All works, but the loop that copies list items one-by-one is rather slow (approximately 1 item per second). Is it possible to speed this up or parallelize it somehow?

Answer 1

Here are three options for speed comparison. We'll create 100 SharePoint list item in a plain list. Then you can see what approach is best.

Option #1. Direct approach

for($i=0; $i -lt 100; $i++){
    Add-PnPListItem -List "Plain List" -Values @{Title=$i}
}

Time: 70.3 seconds

Option #2. Pass list object instead of the name

When using the Add-PnPListItem method, it's best to pass a list as an object instead of the list's title. Otherwise Add-PnPListItem will waste time retrieving the list each time before adding a list item. This approach is about 20% faster:

$list = Get-PnPList -Identity "Plain List"
for($i=0; $i -lt 100; $i++){
    Add-PnPListItem -List $list  -Values @{Title=$i}
}

Time: 56.5 seconds

Option #3. Using batches The fastests approach is to create batches.

$list = Get-PnPList -Identity "Plain List"
$batch = New-PnPBatch
for($i=0; $i -lt 100; $i++){
    Add-PnPListItem -List  $list -Values @{Title=$i} -Batch $batch
}
Invoke-PnPBatch -Batch $batch

Time: 8.0 seconds

Batching seems to be the best approach, but note that:

• you want to keep batches relatively small: ~50-400 items per batch
• error handling for batches is more complicated: if a batch fails - you might not be able to tell what list item caused it to fail.