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.