Performance

In this post, I am going to show you why a PnP PowerShell loop that works fine on twenty items takes an hour on two thousand, what batching does about it, and where batching turns out not to help much.

All numbers below are from one run against a real site on PnP.PowerShell 2.12.0. Your times will differ, the ratios will not.

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. Every one of those is a separate round trip – a request out, a wait, a response back.

That number is worth sitting with. Two hundred items is four minutes. Two thousand is forty. Ten thousand is two hours, assuming nothing throttles you, which at ten thousand separate requests it will.

Reading is cheaper – I measured about 250ms per call in the permissions post – but writing costs about 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. Four minutes becomes thirteen seconds.

The important thing to note is that nothing happens until Invoke-PnPBatch. If your script throws before it reaches that line, none of the work is done. That is usually what you want, but it does mean a partial failure looks like nothing happened 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 pretend I know exactly why, but the shape of it makes sense: an update has to identify the item first, and that work does not disappear just because the calls are bundled. A batched update still costs about 450ms an item against 64ms for a batched insert.

So batching is worth doing for updates – three times faster is three times faster – but if you have read somewhere that batching makes everything twenty times quicker, that is inserts you are reading about.

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

Ten cmdlets on 2.12.0, and three of them are Syntex. In practice batching means list items, group members and fields. Everything else – creating sites, uploading files, changing permissions – is one call at a time and no amount of wishing changes it.

Run that command against your own version rather than trusting this list. It has grown over releases and will keep growing.

Throttling

Push too hard and SharePoint returns 429 Too Many Requests, sometimes 503. This is not a fault, it is the service protecting itself, and the correct response is to wait exactly as long as it tells you to.

The response carries a Retry-After header with a number of seconds. Honour it. Retrying immediately, or backing off on a guess, is what turns brief throttling into a long block.

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 }

Being straight with you: I could not make SharePoint throttle me on demand to test this, so what you are looking at is the shape rather than something I watched recover. The parts that matter are that it only retries on 429 and 503 and rethrows everything else, that it gives up rather than looping forever, and that it reads Retry-After before falling back to its own backoff.

Do not retry on every error. A 404 will never succeed no matter how many times you ask.

What I actually do

• Batch anything that supports it. Nineteen times on inserts is not an optimisation, it 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 – 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.

And time it on a small list before you point it at a big one. Twelve seconds against 242 is the kind of thing you want to find out on 200 items, not on 20,000 🙂