Tag Archives: Batches

Update Large Number of SharePoint Sites with PowerShell Parallel

WIP

Here I’m trying to figure out – how much PowerShell Parallel option is beneficial and how to avoid throttling…

Let us test, how long would it take to create a SharePoint site, if we use regular (sequential) loop or parallelism (I’m creation a sample set of 50 SharePoint Sites in a row):

Regular
(Sequential)
seconds per site
Parallel,
100 sites in batch
seconds per site
Parallel,
500 sites in batch
seconds per site
Regular (Sequential)3.0
Parallel,  ThrottleLimit = 21.600.91
Parallel,  ThrottleLimit = 50.69
Parallel,  ThrottleLimit = 100.2 – 0.3
Parallel,  ThrottleLimit = 200.17

Interesting, but I did not get even one (throttling or any other) error during creation 500 sites.

Get sites details

Now let us test, how long it takes to get sites details with Get-PnPTenantSite (I use a sample set of 500 sites):

Test typeRegular
(Sequential),
seconds per site
Parallel
sample = 100 sites,
seconds per site
Parallel
sample = 200 sites,
seconds per site
Parallel
sample = 500 sites,
seconds per site
Regular (Sequential)0.65
Parallel,  ThrottleLimit = 20.400.330.31
Parallel,  ThrottleLimit = 50.170.140.36 (errors)
Parallel,  ThrottleLimit = 100.11 (errors)0.11 (errors)0.34 (errors)
Parallel,  ThrottleLimit = 200.12 errors+0.07 errors+0.52 (errors)

(errors) means there were small number of errors during test… e.g.

PnP.PowerShell Batches and PowerShell 7 Parallel

Parallelism

Can I use PowerShell 7 “-Parallel” option against SharePoint list items with PnP.PowerShell? Can I run something like:

$items | ForEach-Object -Parallel {
    $listItem = Set-PnPListItem -List "LargeList" -Identity $_ -Values @{"Number" = $(Get-Random -Minimum 100 -Maximum 200 ) }
} 

Yes, sure… But! Since it’s a cloud operation against Microsoft 365 – you will be throttled if you start more than 2 parallel threads! Using just 2 threads does not provide significant performance improvements.

Batching

So, try PnP.PowerShell batches instead. When you use batching, number of requests to the server are much lower. Consider something like:

$batch = New-PnPBatch
1..100 | ForEach-Object{ Add-PnPListItem -List "ItemTest" -Values @{"Title"="Test Item Batched $_"} -Batch $batch }
Invoke-PnPBatch -Batch $batch


Measurements

Adding and setting 100 items with “Add-PnPListItem” and “Set-PnPListItem” in a large (more than 5000 items ) SharePoint list measurements:

Add-PnPListItem
Time per item, seconds
Set-PnPListItem
Time per item, seconds
Regular, without batching1.261.55
Using batches (New-PnPBatch)0.100.80
Using “Parallel” option, with ThrottleLimit 20.690.79
Using “Parallel” option, with ThrottleLimit 30.44 (fails level: ~4/100) 0.53 (fails level: ~3/100)

Adding items with PnP.PowerShell batching is much faster than without batching.

More:

How to delete a large SPO list and/or all items in a large SPO list

Scenario 1: You have a large (>5k items) list in SharePoint Online.
You need to delete this list.

Scenario 2: You have a large (>5k items) list in SharePoint Online.
You need to delete all the list items, but keep the list.

Deleting a large SharePoint Online list

GUI: Microsoft improved SharePoint, so now it takes ~1 second to delete any SharePoint list, including 5000+ items list via GUI.

PowerShell: “Remove-PnPList -Identity $list” command works very fast – ~1 second to delete entire list with >5000 items.

Delete all items in a large SharePoint Online list

In this scenario we need to keep the list, but make it empty (clean it out).

GUI: You can change the list view settings “Item Limit” to <5000, but (at least in my experience) when you try to select, let say, 1000 items and delete them via GUI – it says “775 items were not deleted from large list”:

so this option seems like not a good one.

ShareGate: 3-rd party tools like Sharegate, SysKit give a good results too.

PowerShell

Try this PowerShell command with ScriptBlock:

Get-PnPListItem -List $list -Fields "ID" -PageSize 100 -ScriptBlock { Param($items) $items | Sort-Object -Property Id -Descending | ForEach-Object{ $_.DeleteObject() } } 

or this PowerShell with batches:

$batch = New-PnPBatch
1..12000 | Foreach-Object { Remove-PnPListItem -List $list -Identity $_ -Batch $batch }
Invoke-PnPBatch -Batch $batch

for me both methods gave same good result: ~17 items per second ( ~7 times faster than regular).

PnP.PowerShell batch vs ScriptBlock

How fast are PnP batches? What is better in terms of performance – ScriptBlock or Batching? Here are my measurements:

Time elapsed, secondswith batcheswith scriptBlockwithout batches
Add-PnPListItem (100 items)4-10 seconds42-120 seconds
Add-PnPListItem (500 items)20-40 seconds230-600 seconds
Add-PnPListItem (7000 items)314-560 seconds
Remove-PnPListItem (1000 items)58-103 seconds58 seconds430-1060 seconds
Remove-PnPListItem (7000 items)395-990 seconds397-980 seconds

both – PnP PowerShell batches and ScriptBlocks are 7-10 times faster than plain PnP PowerShell!

Note… For the sake of history: It used to be like that for 5k+ lists:
“Remove-PnPList” fails with a message “The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator”. Deleting with GUI fails too.

References: