Powershell script taking too long to export results to .csv

Question

I have a script that works perfectly until it comes to exporting the csv file. I have tried the script with a sample batch of data (50) and it runs without any issue. However, I have over 70,000 objects in the source data and even breaking it down to multiple csv's with chunks of 10,000 takes an age to get the .csv exported.

I know that the for each part does not take long (10,000 lines took about 20 mins to pull the data) but the bottleneck appears to be the export-csv part. Can anyone suggest any improvements I could make?

N.B The paths marked as HIDDEN are valid in my script but just replaced here for privacy

$mailboxes = @(Import-Csv -Path 'HIDDEN'|get-mailbox)

$report = @()

foreach ($mailbox in $mailboxes)
{
    $stats = Get-MailboxFolderStatistics $mailbox -FolderScope Recoverableitems|where {$_.FolderPath -eq "/DiscoveryHolds"}

    $mbObj = New-Object PSObject
    $mbObj | Add-Member -MemberType NoteProperty -Name "Display Name" -Value $mailbox.DisplayName
    $mbObj | Add-Member -MemberType NoteProperty -Name "UPN" -Value $mailbox.UserPrincipalName
    $mbObj | Add-Member -MemberType NoteProperty -Name "Size" -Value $stats.FolderSize
    $report += $mbObj
}

$report| Export-CSV "HIDDEN"

Answer 1

I don't have access to AD/Exchange, so I tested with the file system. One thought is that you are adding an object into the $mbObject values, when you only need a string that you can then export.

cls
$report = @()
(measure-command {
    Get-ChildItem -Recurse -File -ErrorAction SilentlyContinue | foreach {
        $mbObj = New-Object PSObject
        $mbObj | Add-Member -MemberType NoteProperty -Name "Display Name" -Value $_.Name
        $mbObj | Add-Member -MemberType NoteProperty -Name "UPN" -Value $_.CreationTime
        $mbObj | Add-Member -MemberType NoteProperty -Name "Size" -Value $_.VersionInfo
        $report += $mbObj
    }
}).TotalSeconds
$report.count
(Measure-Command {$report | Export-Csv c:\temp\test.csv -Force}).TotalSeconds

"------------"
$report = @()
(measure-command {
    Get-ChildItem -Recurse -File -ErrorAction SilentlyContinue| foreach {
    $report += [PSCustomObject] @{
                 "Display Name" =  $_.Name.tostring();
                 "UPN" = $_.CreationTime.tostring();
                 "Size" = $_.VersionInfo.tostring()
        }
    }
}).TotalSeconds
$report.count
(Measure-Command {$report | Export-Csv c:\temp\test.csv -Force}).TotalSeconds

I ran this against C:\programData and got these results.

148.5061734
62466
1.0384613
------------
98.8008639
62466
0.564858

The second technique of building $report was a lot faster. If you run that script, you would need to run it twice to populate the system file cache to get good results.

Try that method.

Answer 2

Just try another solution :

"------------"
$report = New-Object System.Collections.ArrayList
(measure-command {
	Get-ChildItem -Recurse -File -ErrorAction SilentlyContinue| foreach {
		$obj = [PSCustomObject] @{
                 "Display Name" =  $_.Name.tostring();
                 "UPN" = $_.CreationTime.tostring();
                 "Size" = $_.VersionInfo.tostring()
		}
        [void]$report.add($obj)
    }
}).TotalSeconds
$report.count
(Measure-Command {$report | Export-Csv c:\temp\test.csv -Force}).TotalSeconds

The result is better :

-- scan + Add-Member --
127,7985037
27327
2,2762346
-- scan + PSCustomObject --
97,3735166
27327
1,7531857
-- scan + ArrayList --
18,6144155
27327
1,497172

Export is done locally so it is still fast.

Same export on a network share ( file size = 12MB) is very, very, very slow (about 235s).

So try other solutions to have the result on a network share :

1. use a stream to write to the network share
2. use a temporay local file and copy the temp file to the network share

"-- write stream --"
(measure-command {
  $stream=[system.io.StreamWriter]$outputfile
  $stream.write( ($arr | convertto-csv -NoTypeInformation -Delimiter ";" ) -join "`r`n" )
  $stream.close()
 }
).TotalSeconds

"-- local export + copy file --"
(measure-command {
  $arr | export-csv $tmpfile -delimiter ";" -encoding unicode -notypeinformation -force
  copy-item -path $tmpfile -destination $outputfile -force
}
).TotalSeconds

The results are much more better and the winner is the copy of the temp file :

-- write stream --
TotalSeconds      : 97,1424451
-- local export + copy file --
TotalSeconds      : 13,4431252

(PS : all these tests have been done with Powershell 5.1)