|

Bulk Updates in PnP PowerShell: Batching, Throttling and Retry

ByG Vijai Kumar

It works just fine when adding twenty items using a loop in PnP PowerShell. Two thousand take forty minutes, and the explanation for that is quite simple – each item requires a separate round trip of one point two seconds each without any overlapping.

That can be solved by batching, except in one case when batching does little to help. Below you can see the actual numbers, taken from a single test run against a real website using PnP.PowerShell 2.12.0. Your results may vary.

The problem, measured

Adding 200 items the obvious way:

1..200 | ForEach-Object {
    Add-PnPListItem -List 'Bulk Test' -Values @{
        Title  = "Single $_"
        Ref    = "S-$('{0:D4}' -f $_)"
        Amount = $_
    }
}
Individual: 242.9s for 200 items
Per item  : 1214ms

1.2 seconds per item. Each and every one of those is a round-trip process—a request and then a response to that request after an interval of waiting time.

This figure deserves contemplation. Two hundred items equal four minutes. Two thousand equal forty minutes. Ten thousand equal two hours, provided that nothing hinders you, which, considering ten thousand requests, it will.

Reading is cheaper, about 250ms per call in the permissions post, but writing costs roughly five times that.

Batching

You build up the operations, then send them together:

$batch = New-PnPBatch

1..200 | ForEach-Object {
    Add-PnPListItem -List 'Bulk Test' -Values @{
        Title  = "Batched $_"
        Ref    = "B-$('{0:D4}' -f $_)"
        Amount = $_
    } -Batch $batch
}

Invoke-PnPBatch -Batch $batch
Batched   : 12.8s for 200 items
Per item  : 64ms

Same 200 items, 19 times faster. 4 Minutes down to 13 seconds.

It all waits for the Invoke-PnPBatch statement to be issued. If an error occurs anywhere prior to that point, then nothing gets done. This is normally how you would want it to happen, but that means partial success shows no success at all.

Where it does not help nearly as much

Same test, updating existing items instead of adding new ones:

Updating one at a time : 285.0s for 200 items
Updating in a batch    :  89.7s for 200 items

3.2 times faster, not nineteen.

I am not going to lie and say that I know the exact reason, but it kind of makes sense since the update query needs to locate the entry, and this part of the job does not go away simply by putting the calls into batches. So even with batching, an update will take about 450ms per entry, which is compared to 64ms per batched insert.

Therefore, batching should be done for updates too, although it will be only three times faster, and not twenty like some people read on the Internet.

Not everything can be batched

This surprised me. Ask the module:

Get-Command -Module PnP.PowerShell |
    Where-Object { $_.Parameters.Keys -contains 'Batch' } |
    Select-Object -ExpandProperty Name
Add-PnPGroupMember
Add-PnPListItem
Invoke-PnPBatch
Invoke-PnPSPRestMethod
Publish-PnPSyntexModel
Remove-PnPField
Remove-PnPListItem
Request-PnPSyntexClassifyAndExtract
Set-PnPListItem
Unpublish-PnPSyntexModel

There are ten cmdlets in 2.12.0, of which three are Syntex. In reality, batching involves list items, groups and their fields. The rest – creation of sites and file uploads and beyond this list – is individual calls.

Invoke that cmdlet against your own release rather than rely on this list. It has evolved and will continue evolving.

Throttling

Try too hard and you will receive error 429 Too Many Requests, and possibly 503 Service Unavailable. It is the service’s own defense mechanism and not a mistake, but it should be dealt with by waiting for as long as instructed.

This response includes the Retry-After header with the number of seconds you have to wait. You should follow this advice; otherwise your requests will be throttled for an extended period of time.

function Invoke-WithRetry {
    param([scriptblock] $Action, [int] $MaxAttempts = 5)

    for ($attempt = 1; $attempt -le $MaxAttempts; $attempt++) {
        try { return & $Action }
        catch {
            $response = $_.Exception.Response
            $status   = $response.StatusCode.value__

            if ($status -ne 429 -and $status -ne 503) { throw }
            if ($attempt -eq $MaxAttempts) { throw }

            $wait = $response.Headers['Retry-After']
            if (-not $wait) { $wait = [math]::Pow(2, $attempt) }

            Write-Warning "Throttled. Waiting $wait seconds (attempt $attempt of $MaxAttempts)."
            Start-Sleep -Seconds $wait
        }
    }
}

Used like this:

Invoke-WithRetry { Invoke-PnPBatch -Batch $batch }

To be honest with you, I could not get SharePoint to throttle me in order to observe it; instead, I am showing you the structure without seeing recovery from that. The important things here are: it tries again only for errors 429 and 503 and throws any other error, it doesn’t keep retrying until the end of time but stops instead, and it tries to find out the delay from Retry-After first.

Do not retry on all kinds of errors. 404 won’t ever succeed whatever you do.

What I actually do

  • Batch anything that supports it. Nineteen times on inserts is the difference between a script you can run and one you cannot.
  • Split very large batches. One batch of 20,000 operations is a single enormous request. A few thousand at a time is easier on everyone and gives you somewhere to resume from.
  • Wrap the invoke, not the loop. Retry Invoke-PnPBatch, not each Add-PnPListItem, because the individual calls are not doing any work yet.
  • Print progress. Anything over about thirty seconds needs to say something, or you will assume it has hung and kill it.

Timing it on a short list before using it on a long list is always smart. Finding out that it will take 12 seconds to process 242 items is something you want to know before it processes 20,000.

Related posts

Similar Posts