Slow script performance and bandwidth amplification O(n^2) when pipeline outputting many large items with SQL server back-end

PowerShell Universal
1

I wanted to provide a reproduction of PSU 4.0.9 performance/bandwidth issue when outputting to the pipeline. This may be helpful for other people experiencing this issue.

Conditions

  • Script outputs to pipeline with many objects of non-trivial sice (example is 4000 items of ~4KB = 16 MB)
  • Set Script properties to keep only a small number of job history
  • Pipeline output goes SQL Server backend triggering high bandwidth usage for duration of script run

Cause
As the pipeline gets filled, it send the whole thing back to SQL Server in an UPDATE statement for the job. In the example below, PSU needs to send to SQL about ~16 - 32 GB (4000 * 16 MB / 2 or ~32 GB) for a 32 MB object.

UPDATE [JobLog] SET [Log] = @p0
OUTPUT 1
WHERE [Id] = @p1;
@p1=3456,@p0=N'[{"Type' . . .

Workaround
Instead of streaming the 4000 objects to the pipeline, create one [pscustomobject] to contain the stream. And then output to pipeline once.

Example Code

param([switch]$StreamArray)
#Test-PSUPipelinePerformanceBug.ps1

# Create 4000 by ~4KB objects
$x = Foreach ($i in (1..4000)) {
    [pscustomobject]@{
        id = $_
        text = 'abcd'*(1KB)
    }
}

if ($StreamArray) {
    # This outputs to pipeline runs with O(N^2) based on number of objects in $x.
    # This estimates to send about 16-30 GB over the wire to SQL
    # because EF Core needs to fetch the full JobLog data first before you can append to it, then send the whole thing back to SQL Server in an UPDATE statement.
    # SLOW - ~58s
    $x
} else {
    # This outputs one object to pipeline and a single update statement which is fast
    # FAST  - ~1s
    [pscustomobject]@{
        objects = $x
    }
}