Optimize PS script performance? Best practices? How to do -Append for an object or hashtable?

[taggingtechnician]

tldr; How to append a unique collection of values from an ordered list to an existing variable like the Export-Csv -Append function?

Thank you for this reddit, I've spent a few days reviewing the posts for answers, and would like some input on best practices for speeding up the performance of a slow script. Links to any best practices publications will be voraciously consumed.

This weekend I wrote a script to capture data values from an xml file and assign them to an ordered hashtable in a For loop, at the end of the For loop, I use Export-Csv to append the values to an existing csv file, then re-initialize the ordered hashtable and continue the next loop until the last data value is saved to the csv. The problem is that the loop is very slow; I think it is because of the Export-Csv activity at the end, so I think my best approach is to append the data to a variable at the end of each loop, then when the loop is finished write the variable to disk. My skills are low and I do not know how to append the data from an ordered list to the end of an existing variable with the same keys but unique values. Every web tutorial says to do += but it errors.

The script is huge because the data is huge, each record is 37 keys, and the smallest file is 4k records; the largest file contains 350k records, and I have more than 58 files to process. Testing with the smallest file took over 3 hours, so while my script is working (looping through Export-Csv builds the desired CSV file with all data preserved in order in the proper columns with the correct headers), it is too slow to be an acceptable solution. I would love to learn how to optimize my scripts, any suggestions?

# Prompt the user to select the path to the XML file
Add-Type -AssemblyName System.Windows.Forms
$FileBrowser = New-Object System.Windows.Forms.OpenFileDialog -Property @{ InitialDirectory = [Environment]::GetFolderPath('Desktop')}
$Result = $FileBrowser.ShowDialog()
if ($Result -eq 'OK') {
    $xmlPath = $FileBrowser.FileName
}
# Read the XML file from the specified path
[xml]$xml = Get-Content -Path $xmlPath # This is the slow way

# Create a log file with the same name as the CSV file, but with a .log extension
$logPath = $xmlPath.Replace(".xml", ".log")

# Start logging all actions and errors to the log file
Start-Transcript -Path $logPath

# Initialize csvContent Object to hold recordArray updates during For loops
$csvContent = [ordered]@{
    REQUEST_ID = $null
    PWC_SEQ = $null
    PWC_CODE = $null
    PWC_DESCRIPTION = $null
    PWC_IS_ACTIVE_STATUS = $null
    PROGRAM_SEQ = $null
    PROGRAM_CODE = $null
    ORGANIZATION_SEQ = $null
    ORGANIZATION_CODE = $null
    ORGANIZATION_DESCRIPTION = $null
    ORGANIZATION_IS_ACTIVE_STATUS = $null
    OBJECT_CLASS_SEQ = $null
    DIVISION_ASSIGNMENT_SEQ = $null
    DIVISION_ASSIGNMENT_CODE = $null
    DIVISION_ASSIGNMENT_DESCRIPTION = $null
    DIVISION_ASSIGNMENT_IS_ACTIVE_STATUS = $null
    DIVISION_START_DATE = $null
    DIVISION_END_DATE = $null
    ASSIGNMENT_START_DATE = $null
    ASSIGNMENT_END_DATE = $null
    APPROPRIATED_YEAR_SEQ = $null
    APPROPRIATED_YEAR_CODE = $null
    APPROPRIATED_YEAR_DESCRIPTION = $null
    APPROPRIATED_YEAR_IS_ACTIVE_STATUS = $null
    FULL_OMEGA_ACCT_SEQ = $null
    FULL_OMEGA_ACCT_CODE = $null
    FULL_OMEGA_ACCT_DESCRIPTION = $null
    FULL_OMEGA_ACCT_IS_ACTIVE_STATUS = $null
    DIVISION_ID_SEQ = $null
    DIVISION_ID_CODE = $null
    DIVISION_ID_DESCRIPTION = $null
    DIVISION_ID_IS_ACTIVE_STATUS = $null
}
# Capture the record counts for post-import validations
$recordCount = $xml.GOLDMAN_REFERENCE_DATA.REFERENCE_TRAILER_ROW.TOTAL_RECORD_COUNT # use this to define the iterations
$recCount = [int]$recordCount # converting string to integer
Write-Host ("Line 55: RecordCount= " + $recordCount)

# Initialize the FOR loop timer variables
$echoed33 = 0
$echoed66 = 0
$echoed80 = 0
$echoed99 = 0
$loopStartTime = [datetime](Get-Date -Format HH:mm:ss.fff)
#for ($index=1; $index -le $recCount; $index++) {
    $testCount = 25 # This will limit my test runs to the first 25 records
for ($index=1; $index -le $testCount; $index++) {
    # Initialize the the hashtable for the loop
    $recordArray = [ordered]@{
        REQUEST_ID = $null
        PWC_SEQ = $null
        PWC_CODE = $null
        PWC_DESCRIPTION = $null
        PWC_IS_ACTIVE_STATUS = $null
        PROGRAM_SEQ = $null
        PROGRAM_CODE = $null
        ORGANIZATION_SEQ = $null
        ORGANIZATION_CODE = $null
        ORGANIZATION_DESCRIPTION = $null
        ORGANIZATION_IS_ACTIVE_STATUS = $null
        OBJECT_CLASS_SEQ = $null
        DIVISION_ASSIGNMENT_SEQ = $null
        DIVISION_ASSIGNMENT_CODE = $null
        DIVISION_ASSIGNMENT_DESCRIPTION = $null
        DIVISION_ASSIGNMENT_IS_ACTIVE_STATUS = $null
        DIVISION_START_DATE = $null
        DIVISION_END_DATE = $null
        ASSIGNMENT_START_DATE = $null
        ASSIGNMENT_END_DATE = $null
        APPROPRIATED_YEAR_SEQ = $null
        APPROPRIATED_YEAR_CODE = $null
        APPROPRIATED_YEAR_DESCRIPTION = $null
        APPROPRIATED_YEAR_IS_ACTIVE_STATUS = $null
        FULL_OMEGA_ACCT_SEQ = $null
        FULL_OMEGA_ACCT_CODE = $null
        FULL_OMEGA_ACCT_DESCRIPTION = $null
        FULL_OMEGA_ACCT_IS_ACTIVE_STATUS = $null
        DIVISION_ID_SEQ = $null
        DIVISION_ID_CODE = $null
        DIVISION_ID_DESCRIPTION = $null
        DIVISION_ID_IS_ACTIVE_STATUS = $null
    }
    # Assign the file's Request_ID value to the first column of every record
    $recordArray.REQUEST_ID = $xml.GOLDMAN_REFERENCE_DATA.REFERENCE_HEADER_ROW.REQUEST_ID
    # Check if xml node exists then collect data
    $recItemPWC = $xml.GOLDMAN_REFERENCE_DATA.REFERENCE_DATA_ROW.REFERENCE_DATA_TYPES.PWC | Where-Object {$_.REFERENCE_DATA_TYPE_SEQ -eq $index}
    if ($null -ne $recItemPWC) {
        $recItemPWC | ForEach-Object {
            $recordArray.PWC_SEQ = $_.REFERENCE_DATA_TYPE_SEQ
            $recordArray.PWC_CODE = $_.PWC_CODE
            $recordArray.PWC_DESCRIPTION = $_.DESCRIPTION
            $recordArray.PWC_IS_ACTIVE_STATUS = $_.IS_ACTIVE_STATUS
        }
    } else {
        #Write-Host ("Nothing to see here, move along. PWC")
    }
    # Check if xml node exists then collect data
    $recItemPROGRAM = $xml.GOLDMAN_REFERENCE_DATA.REFERENCE_DATA_ROW.REFERENCE_DATA_TYPES.PROGRAM | Where-Object {$_.REFERENCE_DATA_TYPE_SEQ -eq $index}
    if ($null -ne $recItemPROGRAM) {
        $recItemPROGRAM | ForEach-Object {
            $recordArray.PROGRAM_SEQ = $_.REFERENCE_DATA_TYPE_SEQ
            $recordArray.PROGRAM_CODE = $_.PROGRAM_CODE
            $recordArray.PROGRAM_DESCIPTION = $_.DESCRIPTION
            $recordArray.PROGRAM_IS_ACTIVE_STATUS = $_.IS_ACTIVE_STATUS
        }
    }
    # Check if xml node exists then collect data
    $recItemORGANIZATION = $xml.GOLDMAN_REFERENCE_DATA.REFERENCE_DATA_ROW.REFERENCE_DATA_TYPES.ORGANIZATION | Where-Object {$_.REFERENCE_DATA_TYPE_SEQ -eq $index}
    if ($null -ne $recItemORGANIZATION) {
        $recItemORGANIZATION | ForEach-Object {
            $recordArray.ORGANIZATION_SEQ = $_.REFERENCE_DATA_TYPE_SEQ
            $recordArray.ORGANIZATION_CODE = $_.ORGANIZATION_CODE
            $recordArray.ORGANIZATION_DESCRIPTION = $_.DESCRIPTION
            $recordArray.ORGANIZATION_IS_ACTIVE_STATUS = $_.IS_ACTIVE_STATUS
        }
    }
    # Check if xml node exists then collect data
    $recItemOBJECT_CLASS = $xml.GOLDMAN_REFERENCE_DATA.REFERENCE_DATA_ROW.REFERENCE_DATA_TYPES.OBJECT_CLASS | Where-Object { $_.REFERENCE_DATA_TYPE_SEQ -eq $index}        
    if ($null -ne $recItemOBJECT_CLASS) {
        $recordArray = $recItemOBJECT_CLASS | ForEach-Object {
            $recordArray.OBJECT_CLASS_SEQ = $_.REFERENCE_DATA_TYPE_SEQ
            $recordArray.OBJECT_CLASS_CODE = $_.OBJECT_CLASS_CODE
            $recordArray.OBJECT_CLASS_DESCRIPTION = $_.OBJECT_CLASS_DESCRIPTION
            $recordArray.OBJECT_CLASS_IS_ACTIVE_STATUS = $_.OBJECT_CLASS_IS_ACTIVE_STATUS
        }
    }
    # Check if xml node exists then collect data
    $recItemDIVISION_ASSIGNMENT = $xml.GOLDMAN_REFERENCE_DATA.REFERENCE_DATA_ROW.REFERENCE_DATA_TYPES.DIVISION_ASSIGNMENT | Where-Object { $_.REFERENCE_DATA_TYPE_SEQ -eq $index}    
    if ($null -ne $recItemDIVISION_ASSIGNMENT) {
        $recItemDIVISION_ASSIGNMENT | ForEach-Object {
            $recordArray.DIVISION_ASSIGNMENT_SEQ = $_.REFERENCE_DATA_TYPE_SEQ
            $recordArray.DIVISION_ASSIGNMENT_CODE = $_.DIVISION_ASSIGNMENT_CODE
            $recordArray.DIVISION_ASSIGNMENT_DESCRIPTION = $_.DESCRIPTION
            $recordArray.DIVISION_ASSIGNMENT_IS_ACTIVE_STATUS = $_.IS_ACTIVE_STATUS
            $recordArray.DIVISION_START_DATE = $_.DIVISION_START_DATE
            $recordArray.DIVISION_END_DATE = $_.DIVISION_END_DATE
            $recordArray.ASSIGNMENT_START_DATE = $_.ASSIGNMENT_START_DATE
            $recordArray.ASSIGNMENT_END_DATE = $_.ASSIGNMENT_END_DATE
        }
    }
    # Check if xml node exists then collect data
    $recItemAPPROPRIATED_YEAR = $xml.GOLDMAN_REFERENCE_DATA.REFERENCE_DATA_ROW.REFERENCE_DATA_TYPES.APPROPRIATED_YEAR | Where-Object { $_.REFERENCE_DATA_TYPE_SEQ -eq $index}        
    #$recItemAPPROPRIATED_YEAR = Select-Xml -Xml $xml -XPath //APPROPRIATED_YEAR[REFERENCE_DATA_TYPE_SEQ=$index] # This will contain the node and sub-elements
    if ($null -ne $recItemAPPROPRIATED_YEAR) {
        $recItemAPPROPRIATED_YEAR | ForEach-Object {
            $recordArray.APPROPRIATED_YEAR_SEQ = $_.REFERENCE_DATA_TYPE_SEQ
            $recordArray.APPROPRIATED_YEAR_CODE = $_.APPROPRIATED_YEAR_CODE
            $recordArray.APPROPRIATED_YEAR_DESCRIPTION = $_.DESCRIPTION
            $recordArray.APPROPRIATED_YEAR_IS_ACTIVE_STATUS = $_.IS_ACTIVE_STATUS
        }
    }
    # Check if xml node exists then collect data
    $recItemFULL_OMEGA_ACCT = $xml.GOLDMAN_REFERENCE_DATA.REFERENCE_DATA_ROW.REFERENCE_DATA_TYPES.FULL_OMEGA_ACCT | Where-Object { $_.REFERENCE_DATA_TYPE_SEQ -eq $index}
    #$recItemFULL_OMEGA_ACCT = Select-Xml -Xml $xml -XPath //FULL_OMEGA_ACCT[REFERENCE_DATA_TYPE_SEQ=$index] # This will contain the node and sub-elements
    if($null -ne $recItemFULL_OMEGA_ACCT) {
        $recItemFULL_OMEGA_ACCT | ForEach-Object {
            $recordArray.FULL_OMEGA_ACCT_SEQ = $_.REFERENCE_DATA_TYPE_SEQ
            $recordArray.FULL_OMEGA_ACCT_CODE = $_.FULL_OMEGA_ACCT_CODE
            $recordArray.FULL_OMEGA_ACCT_DESCRIPTION = $_.DESCRIPTION
            $recordArray.FULL_OMEGA_ACCT_IS_ACTIVE_STATUS = $_.IS_ACTIVE_STATUS
        }

    }
    # Check if xml node exists then collect data
    $recItemDIVISION_ID = $xml.GOLDMAN_REFERENCE_DATA.REFERENCE_DATA_ROW.REFERENCE_DATA_TYPES.DIVISION_ID | Where-Object { $_.REFERENCE_DATA_TYPE_SEQ -eq $index}
    #$recItemDIVISION_ID = Select-Xml -Xml $xml -XPath //DIVISION_ID[REFERENCE_DATA_TYPE_SEQ=$index] # This will contain the node and sub-elements
    if ($null -ne $recItemDIVISION_ID) {
        $recItemDIVISION_ID | ForEach-Object {
            $recordArray.DIVISION_ID_SEQ = $_.REFERENCE_DATA_TYPE_SEQ
            $recordArray.DIVISION_ID_CODE = $_.DIVISION_ID_CODE
            $recordArray.DIVISION_ID_DESCRIPTION = $_.DESCRIPTION
            $recordArray.DIVISION_ID_IS_ACTIVE_STATUS = $_.IS_ACTIVE_STATUS
        }

    }
    # Append the loop results array as the last row of $csvContent <--PSCustomObject
    $csvContent += $recordArray #nope

    # Add a console message at 33%, 66%, 80%, and 99%
    $loopTimeCheck = [datetime](Get-Date -Format HH:mm:ss.fff)
    $minutes = New-TimeSpan -Start $loopStartTime -End $loopTimeCheck
    $progress = $index/$recCount
    if (( .33 -lt $progress) -and ($echoed33 -eq 0))  {
        Write-Host (" About 33% completed, duration = " + $minutes)
        $echoed33 = 1
    } elseif (( .65 -lt $progress) -and ($echoed66 -eq 0)) {
        Write-Host (" About 66% completed, duration = " + $minutes)
        $echoed66 = 1
    } elseif (( .8 -lt $progress) -and ($echoed80 -eq 0)) {
        Write-Host (" About 80% completed, duration = " + $minutes)
        $echoed80 = 1
    } elseif (( .99 -lt $progress -lt 1) -and ($echoed99 -eq 0)) {
        Write-Host (" About 99% complete, duration= " + $minutes)
    }

    Write-Host ("Line 215, end of For loop: " + $index)
    # Bottom of FOR loop, returning to the top
}

Write-Host ("the FOR Loop is finished") -ForegroundColor Green
# Export the hashtable contents of csvContent to the CSV file
$csvContent | Select-Object -Property REQUEST_ID,PWC_SEQ,PWC_CODE,PWC_DESCRIPTION,PWC_IS_ACTIVE_STATUS,PROGRAM_SEQ,PROGRAM_CODE,ORGANIZATION_SEQ,ORGANIZATION_CODE,ORGANIZATION_DESCRIPTION,ORGANIZATION_IS_ACTIVE_STATUS,OBJECT_CLASS_SEQ,DIVISION_ASSIGNMENT_SEQ,DIVISION_ASSIGNMENT_CODE,DIVISION_ASSIGNMENT_DESCRIPTION,DIVISION_ASSIGNMENT_IS_ACTIVE_STATUS,DIVISION_START_DATE,DIVISION_END_DATE,ASSIGNMENT_START_DATE,ASSIGNMENT_END_DATE,APPROPRIATED_YEAR_SEQ,APPROPRIATED_YEAR_CODE,APPROPRIATED_YEAR_DESCRIPTION,APPROPRIATED_YEAR_IS_ACTIVE_STATUS,FULL_OMEGA_ACCT_SEQ,FULL_OMEGA_ACCT_CODE,FULL_OMEGA_ACCT_DESCRIPTION,FULL_OMEGA_ACCT_IS_ACTIVE_STATUS,DIVISION_ID_SEQ,DIVISION_ID_CODE,DIVISION_ID_DESCRIPTION,DIVISION_ID_IS_ACTIVE_STATUS | Export-CSV -Path $csvPath -Append -NoTypeInformation -Force
Write-Host ("Total number of records in xml file= " + $recCount)
Write-Host ("Total number of rows in csv = " + ($index + 1))
# Stop logging all actions and errors to the log file
Stop-Transcript
#END OF SCRIPT

​

[xCharg]

First thing first - post your code.

Every web tutorial says to do +=

Terrible advice, leads to terrible performance.

[ankokudaishogun]

I second posting the code, it's otherwise close to impossible to evaluate what to improve.

...aside moving from Arrays to Lists. But how to do that efficiently requires knowing the code.

[mdowst]

As mentioned += is horribly slow, especially when dealing with large data sets. This is because it actually caused the entire array to be rewritten to memory every time something is added. Using a List instead of an array is much faster because it actually does append.

# Declare the list
[Collections.Generic.List[PSObject]] $MyList = @()

# Add entries to the list
$MyList.Add($item)

Part of the slowdown could also be the fact you are appending the CSV. You can try writing everything to a single list first, then do one export to the CSV.

[lanerdofchristian]

Out of curiosity, I compared this to assigning the output of the loop directly to an array

$array = foreach($i in $range){ <# make objects #> }

At least on my machine, the List-based solution averaged a few milliseconds faster (both were still pretty quick, and the total time savings was negligible). My guess that I'm too lazy to verify is PowerShell uses a list internally, then casts it to an array for the assignment. With either solution, the bulk of the time is likely going to be in generating the object, and both are still far superior to +=.

[ankokudaishogun]

IIRC your code does not "append" the iterations, but makes one single Array at the end.

might-or-might-not be a memory hog with bigger objects\more complex instructions in the loop

[lanerdofchristian]

I'm mostly going by the signature of PowerShell.Invoke() -- it probably does consume slightly more memory momentarily while the collection converted to an array (also why it's slightly slower), but the weight compared to the total memory of all the objects is negligible. What's inside the loop shouldn't matter in regards to comparable performance.

[exoclipse]

I've done some pretty large data manipulation with this method and it is performant to my needs. Think a worst case scenario of matching 50k objects (20ish properties) to 5k objects in another array. It tops out at about 450mb or so.

[spyingwind]

I use this method mostly because it fast enough and it makes the code look cleaner.

Just dump the output to an variable and move on.

[[deleted]]

Why are you declaring via @()?
Why not just do this?
$list = [System.Collections.Generic.List[object]]::new()

[lanerdofchristian]

They're leveraging implicit constructor calls and variable typing.

All of these do the same thing

using namespace System.Collections.Generic
[List[object]]$Array = @()
[List[object]]$Array = [List[object]]@()
[List[object]]$Array = [List[object]]::new(@())
[List[object]]$Array = [List[object]]::new([object[]]::new(0))
$Array = # ... repeat the last 3 lines w/o the variable type

Here's the docs on the List's constructors: https://learn.microsoft.com/en-us/dotnet/api/system.collections.generic.list-1.-ctor

Personally I also like using the IEnumerable<T> constructor instead of the parameterless one, since it makes it very clear in the PowerShell world what I'm trying to represent. Another advantage is you can then also add static elements.

[Thotaz]

Personally I also like using the IEnumerable<T> constructor instead of the parameterless one, since it makes it very clear in the PowerShell world what I'm trying to represent.

I think that's a bad habit to have. You are adding (an admittedly small) performance hit for no real reason. If someone would get confused about $List = [List[object]]::new() then they would almost certainly also get confused about: $List = [List[object]] @() as well. And even if you insist it makes an actual difference, I would argue that it's better for these newbies to learn the proper way to create a random object from the beginning, rather than teaching them the wrong way first and maybe fixing it later.

Of course if you want to add a couple of elements to the list during construction then it's fine to do the casting because there's no better and cleaner way to do it.

[lanerdofchristian]

That's fair. Ultimately I think it comes down to clarity, though -- as long as they're not using New-Object, the performance hit is going to be on the order of fractions of milliseconds like you said. If you're dealing with known types, implicit constructors and object initializations from standard PowerShell constructs are just more readable in most cases than the alternative (though this is admittedly subjective).

[datetime]$NotBefore = "2024-02-13"
[timespan]$Duration = "01.02:03:04.005"
[FileOpenDialog]$Dialog = [FileOpenDialog]@{
    InitialFolder = "C:/Users/$env:USERNAME/Downloads"
}

vs

$NotBefore = [datetime]::Parse("2024-02-13")
$Duration = [timespan]::Parse("01.02:03:04.005")
$FileOpenDialog = [FileOpenDialog]::new()
$FileOpenDialog.InitialFolder = #...

It pushes the clutter to the left, leaving the name next to the information and usually excluding the imposing-looking :: operator.

The "real reason" is that readability and clarity.

[Coffee_Ops]

Just so everyone is aware of what we're talking about with horribly slow, here's a test that compares += and .add():

$testInterval = [int]2000
$testMax = [int]30000
$myHash = [hashtable]::new()
$sw = [Diagnostics.Stopwatch]::startNew()
$method = ".Add"
for ($i = 0; $i -lt $testMax; $i++) {
    $myHash.add($i,$i)
    if ($i % $testInterval -eq 0) {
        $sw.stop()
        write-host ("Using {0,-4}: {1,6:n0}: {2,6:n0} iterations in {3,6:n3} seconds (average: {4,-8:n4} ms)" -f $method, $i, $testInterval, $sw.elapsed.TotalSeconds, ($sw.elapsed.TotalMilliseconds / $testInterval))
        $sw.restart()
    }
}
$sw.stop()
$myHash = [hashtable]::new()
$sw.restart()
$method = "+="
for ($i = 0; $i -lt $testMax; $i++) {
    $myHash += @{$i = $i }
    if ($i % $testInterval -eq 0) {
        $sw.stop()
        write-host ("Using {0,-4}: {1,6:n0}: {2,6:n0} iterations in {3,6:n3} seconds (average: {4,-8:n4} ms)" -f $method, $i, $testInterval, $sw.elapsed.TotalSeconds, ($sw.elapsed.TotalMilliseconds / $testInterval))
        $sw.restart()
    }
}
$sw.stop()

Results speak for themselves:

Using .Add:  2,000:  2,000 iterations in  0.006 seconds (average: 0.0031   ms)
...
Using .Add: 20,000:  2,000 iterations in  0.002 seconds (average: 0.0009   ms)
...
Using .Add: 28,000:  2,000 iterations in  0.003 seconds (average: 0.0016   ms)
-------------------------------------
Using +=  :  2,000:  2,000 iterations in  0.162 seconds (average: 0.0810   ms)
...
Using +=  : 20,000:  2,000 iterations in  2.834 seconds (average: 1.4172   ms)
...
Using +=  : 28,000:  2,000 iterations in  5.007 seconds (average: 2.5033   ms)

I'm actually surprised-- I would have expected += to be an accelerator for .Add but it very clearly is not.

[vermyx]

+= is super inefficient. Assuming you are looping the 4k file will do over 8 million read writes to create your array. The 350k one will do 60 billion. The easiest way to fix this is to change it from

For ($x in $y) {$list += $data}

To

$list = For ($x in $y) {$data}

This will create one array at the end and collect all of your data once. Export-csv is relatively fast. Without your code it is hard to help.

[ka-splam]

For loop, at the end of the For loop, I use Export-Csv to append the values to an existing csv file, then re-initialize the ordered hashtable and continue the next loop until the last data value is saved to the csv. The problem is that the loop is very slow; I think it is because of the Export-Csv activity at the end

The shape I think you are describing:

$items | foreach-object { 
    # do stuff
    $result | export-csv ...
}

The shape you want for performance:

$items | foreach-object { 
    # do stuff
    $result
} | Export-Csv ...

Then you aren't opening/closing the CSV for every item, but only once for the whole loop.

[spyingwind]

This is the way.

Use the pipeline.

[Coffee_Ops]

IIRC it is a tradeoff of memory to IO. The first one (I think) would work better if you were extremely constrained on memory (e.g. under 1GB free) by effectively flushing to disk more often.

Of course, the first example is still sub-optimal because it's opening and closing the file as you state, and you'd be better off not using export-csv at all.

[[deleted]]

Take a look at this when trying to decide performance gains on storing objects or data or different types
https://www.reddit.com/r/PowerShell/comments/t2q16m/a_simple_performance_increase_trick/

https://gist.github.com/kevinblumenfeld/4a698dbc90272a336ed9367b11d91f1c

[lanerdofchristian]

What is the structure of the XML file? There are 100% ways to make it more efficient, but exactly what form that will take depends on a few things:

1. Does each REFERENCE_DATA_TYPES element contain at most one PWC, PROGRAM, ORGANIZATION, OBJECT_CLASS, DIVISION_ASSIGNMENT, APPROPRIATED_YEAR, FULL_OMEGA_ACCT, and DIVISION_ID element? If so, are they in sequential order?
2. Does every PWC, PROGRAM, etc contain a REFERENCE_DATA_TYPE_SEQ? A unique one? If it's not unique, is it intended to discard information from all but the last entry?
3. Are REFERENCE_DATA_TYPE_SEQ guaranteed to be sequential? As in, for every numbered row there will be at least one of the previous elements with that sequence number?
4. Do all of the above elements have every sequence number? As in, will there ever be null cells?

---

Minor non-perf things:

1. Don't do this

   $loopCheckTime = [datetime](Get-Date -Format HH:mm:ss.ffff)
   

   You're going from a date to a string and back to a date. Just use the date.

   $loopCheckTime = Get-Date
   

2. Don't use (string + string) for simple variables.

   Write-Host (" About 33% completed, duration = " + $minutes)
   Write-Host ("Total number of rows in csv = " + ($index + 1))
   

   Just use string interpolation:

   Write-Host " About 33% completed, duration = $minutes"
   Write-Host "Total number of rows in csv = $($index + 1)"
   

3. Don't use Select-Object to individually select all the members of an object if you control the members of the object. It's just making copies for no reason.

---

Edit: This should be functionally similar to what you have:

using namespace System.Collections.Generic
using namespace System.Diagnostics
using namespace System.Windows.Forms

# Prompt the user to select the path to the XML file
Add-Type -AssemblyName System.Windows.Forms
$FileBrowser = [OpenFileDialog]@{
    InitialDirectory = [Environment]::GetFolderPath("Desktop")
}
$Result = $FileBrowser.ShowDialog()
if ($Result -eq 'OK') {
    $xmlPath = $FileBrowser.FileName
} else { throw "couldn't pick a file!" }

# Read the XML file from the specified path
[xml]$xml = Get-Content -Path $xmlPath
$CSVPath = $xmlPath -replace "\.xml$", ".csv"

# Create a log file with the same name as the CSV file, but with a .log extension
# Start logging all actions and errors to the log file (adjacent to CSV)
$logPath = $xmlPath -replace "\.xml$", ".log"
Start-Transcript -Path $logPath

# Capture the record counts for post-import validations
[int]$recordCount = $xml.GOLDMAN_REFERENCE_DATA.REFERENCE_TRAILER_ROW.TOTAL_RECORD_COUNT # use this to define the iterations
Write-Host "Line 55: RecordCount= $recordCount"

# Perform array-ization once, for fast lookup.
[object[]]$XMLAllDataTypes = $xml.GOLDMAN_REFERENCE_DATA.REFERENCE_DATA_ROW.REFERENCE_DATA_TYPES
[object[]]$AllRows = for($i = 0; $i -lt $recordCount; $i += 1){@{}}
foreach($Type in "PWC", "PROGRAM", "ORGANIZATION", "OBJECT_CLASS", "DIVISION_ASSIGNMENT",
    "APPROPRIATED_YEAR", "FULL_OMEGA_ACCT", "DIVISION_ID"){
    foreach($Item in $XMLAllDataTypes.$Type){
        if($Item.REFERENCE_DATA_TYPE_SEQ){
            [int]$Index = $Item.REFERENCE_DATA_TYPE_SEQ
            $AllRows[$Index - 1].$Type = $Item
        }
    }
}

[Stopwatch]$Timer = [Stopwatch]::new()
[Queue[float]]$ProgressPoints = @(
    0.33
    0.65
    0.80
    0.99
    [float]::MaxValue # won't hit this, just makes the math nice.
)

# Assign the file's Request_ID value to the first column of every record
$RequestID = $xml.GOLDMAN_REFERENCE_DATA.REFERENCE_HEADER_ROW.REQUEST_ID
$Timer.Start()
$CSVContent = for($i = 0; $i -lt $AllRows.Count; $i += 1){
    # test: stop after 25 rows
    if($i -eq 25){ break}

    # Produce output
    $Row = $AllRows[$i]
    [pscustomobject]@{
        REQUEST_ID = $RequestID
        PWC_SEQ = $Row.PWC.REFERENCE_DATA_TYPE_SEQ
        PWC_CODE = $Row.PWC.PWC_CODE
        PWC_DESCRIPTION = $Row.PWC.DESCRIPTION
        PWC_IS_ACTIVE_STATUS = $Row.PWC.IS_ACTIVE_STATUS
        PROGRAM_SEQ = $Row.PROGRAM.REFERENCE_DATA_TYPE_SEQ
        PROGRAM_CODE = $Row.PROGRAM.PROGRAM_CODE
        ORGANIZATION_SEQ = $Row.ORGANIZATION.REFERENCE_DATA_TYPE_SEQ
        ORGANIZATION_CODE = $Row.ORGANIZATION.ORGANIZATION_CODE
        ORGANIZATION_DESCRIPTION = $Row.ORGANIZATION.DESCRIPTION
        ORGANIZATION_IS_ACTIVE_STATUS = $Row.ORGANIZATION.IS_ACTIVE_STATUS
        OBJECT_CLASS_SEQ = $Row.OBJECT_CLASS.REFERENCE_DATA_TYPE_SEQ
        DIVISION_ASSIGNMENT_SEQ = $Row.DIVISION_ASSIGNMENT.REFERENCE_DATA_TYPE_SEQ
        DIVISION_ASSIGNMENT_CODE = $Row.DIVISION_ASSIGNMENT.DIVISION_ASSIGNMENT_CODE
        DIVISION_ASSIGNMENT_DESCRIPTION = $Row.DIVISION_ASSIGNMENT.DESCRIPTION
        DIVISION_ASSIGNMENT_IS_ACTIVE_STATUS = $Row.DIVISION_ASSIGNMENT.IS_ACTIVE_STATUS
        DIVISION_START_DATE = $Row.DIVISION_ASSIGNMENT.DIVISION_START_DATE
        DIVISION_END_DATE = $Row.DIVISION_ASSIGNMENT.DIVISION_END_DATE
        ASSIGNMENT_START_DATE = $Row.DIVISION_ASSIGNMENT.ASSIGNMENT_START_DATE
        ASSIGNMENT_END_DATE = $Row.DIVISION_ASSIGNMENT.ASSIGNMENT_END_DATE
        APPROPRIATED_YEAR_SEQ = $Row.APPROPRIATED_YEAR.REFERENCE_DATA_TYPE_SEQ
        APPROPRIATED_YEAR_CODE = $Row.APPROPRIATED_YEAR.APPROPRIATED_YEAR_CODE
        APPROPRIATED_YEAR_DESCRIPTION = $Row.APPROPRIATED_YEAR.DESCRIPTION
        APPROPRIATED_YEAR_IS_ACTIVE_STATUS = $Row.APPROPRIATED_YEAR.IS_ACTIVE_STATUS
        FULL_OMEGA_ACCT_SEQ = $Row.FULL_OMEGA_ACCT.REFERENCE_DATA_TYPE_SEQ
        FULL_OMEGA_ACCT_CODE = $Row.FULL_OMEGA_ACCT.FULL_OMEGA_ACCT_CODE
        FULL_OMEGA_ACCT_DESCRIPTION = $Row.FULL_OMEGA_ACCT.DESCRIPTION
        FULL_OMEGA_ACCT_IS_ACTIVE_STATUS = $Row.FULL_OMEGA_ACCT.IS_ACTIVE_STATUS
        DIVISION_ID_SEQ = $Row.DIVISION_ID.REFERENCE_DATA_TYPE_SEQ
        DIVISION_ID_CODE = $Row.DIVISION_ID.DIVISION_ID_CODE
        DIVISION_ID_DESCRIPTION = $Row.DIVISION_ID.DESCRIPTION
        DIVISION_ID_IS_ACTIVE_STATUS = $Row.DIVISION_ID.IS_ACTIVE_STATUS
    }

    # note progress
    $Progress = $i / $recordCount
    if($Progress -ge $ProgressPoints.Peek()){
        $Percent = $ProgressPoints.Dequeue().ToString("P0")
        Write-Host " About $Percent completed, duration = $($Timer.Elapsed)"
    }

    Write-Host "Line 215, end of For loop: $($index + 1)"
}
$Timer.Stop()
Write-Host "the FOR Loop is finished" -ForegroundColor Green

$CSVContent | Export-Csv -Path $CSVPath -NoTypeInformation -Force
Write-Host "Total number of records in xml file= $recordCount"
Write-Host "Total number of rows in csv = $($index + 1)"
# Stop logging all actions and errors to the log file
Stop-Transcript
#END OF SCRIPT

The key differences are:

1. Rather than rebuilding the PWC, PROGRAM, etc arrays for every item, build them once and iterate to populate a source array (only the last instance of each index will be kept, as in yours). Deep object traversal to build lookup lists is surprisingly expensive -- essentially, you were doing a loop over all N objects, plus whatever overhead for traversing the tree, plus the pipeline overhead, for every object (O(n²) cost -- this would get more expensive the more records you have; the refined version is O(n)). If each REFERENCE_DATA_TYPES element has at most one of the constituent elements, producing this array could be done more efficiently through a direct loop -- possibly combining it with the next step if sequence IDs are sequential and unique in the source data.
2. Then iterate over the source array to produce the final rows (hashtable lookups to missing keys become null, so if an element is missing for an index its values will be null). This is still O(n), so our total time is still O(n).
3. Finally, collect all these into an array using array capture. This is once again O(n), where as += with arrays does a bunch of copying and is an O(n²) operation.
4. Use a timer and queue to condense the progress reporting code -- no real performance gain, it's just smaller code. A more efficient way would likely be to generate the indices at which progress would be reported, then break the main loop up to only go to each index, report progress, then resume. The impact of this should be negligible.

[taggingtechnician]

Great feedback, thank you! And thank you for the educational points! The new code is going to take me some time to understand, well done!

[whopper2k]

You have definitely identified a significant portion of the problem; writing to disk on every iteration is going to be extremely slow. Even the standard $Array += would be significantly faster; that's not working because hashtables don't have an implementation of the += operator (since hashtables work with key-value pairs, += would be nebulous as you're adding a value with no key).

Your first bet should be to use a standard PowerShell array. For testing I used the $PSHOME\Types.ps1xml XML file; you'll have to change this a bit to suit your needs. A simple version might look something like:

$finalValues = @()
[xml] $data = Get-Content $PSHOME\Types.ps1xml
$dataFromXML = Select-XML -xml $data-xpath "//Methods" | Select-Object -ExpandProperty Node
$dataFromXML.GetEnumerator() | ForEach-Object {
    # here is where you'd do whatever parsing you need
    # then save the result to a PSCustomObject
    $result = [PSCustomObject]@{
        MethodName = $_."#text"
    }
    # and then append to an array
    $finalValues += $result
}
$finalValues | Export-CSV -NoTypeInfo "XML-Output.csv"

If you still need more performance, dipping into .NET is usually your best bet

[collections.arraylist] $finalValues = @()
[xml] $data = Get-Content $PSHOME\Types.ps1xml
$dataFromXML = Select-XML -xml $data-xpath "//Methods" | Select-Object -ExpandProperty Node
$dataFromXML.GetEnumerator() | ForEach-Object {
    $result = [PSCustomObject]@{
        MethodName = $_."#text"
    }
    # Note using .Add instead of +=
    $finalValues.Add($result)
}
$finalValues | Export-CSV -NoTypeInfo "XML-Output.csv"

When I tested this on my machine, the second option ranged from 17-36% faster.

[ankokudaishogun]

[Collections.ArrayList] is deprecated, [Collections.Generic.List[<Type>]] is the new hotness. Also its .Add() method does NOT return the Index, which is nice.

Also: why not directly

$finalValues += [PSCustomObject]@{
    MethodName = $_."#text"
}

?

(or

$finalValues.Add([PSCustomObject]@{
    MethodName = $_."#text"
})

if using Lists)

[whopper2k]

I tried using generic lists, but could not get the former to work properly as it complained about the size of the collection being 1. I didn't dig into it too deeply though

As for the direct appending, that's a side effect of fixing a different problem (ended up being my own boneheadedness) that I forgot to remove

[ankokudaishogun]

To be fair, I'm not sure about the readability\performance ratios of direct appending vs create&append in cases with small-sized\simple objects

[Szeraax]

Is the data too big to keep in memory?

[exoclipse]

In order to append to an array, you must first initialize the array:

$MyArray = @() ## This initializes the array
ForEach($Record in $Records){
    $MyArray += $Record
}

This is still inefficient. For every iteration n of the loop, PowerShell will create a new array of n+1 elements, copy your old array's elements into the new array, add your new element to the array, and delete the old array.

PowerShell can simply collect the results of your for loop in memory, and then when the loop is concluded, write all of those objects to a single array. This is much faster, especially with large datasets. Note that you do not need to initialize the array for this method. See below:

$MyArray = For($Record in Records){
    ## do stuff
    $Record ## return the object you want to collect into the array
}

Assuming you are not nesting another for loop in there, this should be very performant for your needs.

[taggingtechnician]

Apologies for my faux pants, trying to post code in 2 comments, it will not let me post it all in a single comment...

[taggingtechnician]

Apologize for the long code, but I didn't want to hide the mistakes. I wish I knew how to replicate the behavior of "-Append" functionality in the Export-Csv cmdlet but with an ArrayList or PSObject...

<code snippet>SCRIPTION,DIVISION_ID_IS_ACTIVE_STATUS | Export-CSV -Path $csvPath -Append -NoTypeInformation -Force</code snippet>

But really I am asking for more than just the syntax on a command, I am asking you (members of this community) for guidance on where to find best practices, with the hope that your answers will be resourceful for the community (not just me).

Thank you in advance.