Powershell script taking too long to export results to .csv

Sharon Beckett 1 Reputation point
2023-01-13T13:29:54.7966667+00:00

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"
Microsoft Exchange Online
Microsoft Exchange Online Management
Microsoft Exchange Online Management
Microsoft Exchange Online: A Microsoft email and calendaring hosted service.Management: The act or process of organizing, handling, directing or controlling something.
4,565 questions
Exchange Server Management
Exchange Server Management
Exchange Server: A family of Microsoft client/server messaging and collaboration software.Management: The act or process of organizing, handling, directing or controlling something.
7,661 questions
{count} votes

2 answers

Sort by: Most helpful
  1. MotoX80 34,346 Reputation points
    2023-01-13T15:58:46.0433333+00:00

    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.


  2. MELIN Christophe 0 Reputation points
    2024-10-17T12:41:48.4633333+00:00

    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)

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.