How to make an in-variable change to objects that isn't slow with PSCustomObjects?

Question

I have two tables as pscustomobjects and I tried doing the equivalent of a SQL join to add some properties back into the primary object that I need to read from. The issue is that my code below ran on an object collection for 5 hours with about 40,000 entries and still didn't finish. Is there something I'm doing wrong?

$tableObj = import-csv ".\employeeIDsAndAttributes.csv"

"Getting AD data"
$directoryTable = Get-ADUser -Filter {(employeeid -like "*")} -Properties employeeid,name,samaccountname,distinguishedname | 
Select-Object employeeid,name,samaccountname,distinguishedname
"Finished getting AD data. Joining tables."

foreach ($changeRecordLine in $tableObj) {
    $changeRecordLine | add-member -NotePropertyName "Name" -NotePropertyValue ($directoryTable | Where-Object {($_.employeeid -eq $changeRecordLine.employeeID)} | Select-Object -ExpandProperty name) -Force
    $changeRecordLine | add-member -NotePropertyName "DN" -NotePropertyValue ($directoryTable | Where-Object {($_.employeeid -eq $changeRecordLine.employeeID)} | Select-Object -ExpandProperty distinguishedname) -Force
    $changeRecordLine | add-member -NotePropertyName "ParentDN" -NotePropertyValue ( $changeRecordLine.DN.substring($changeRecordLine.Name.length+4)) -Force
}

Excel let me join my columns without problems by using vlookup, but this should have been fast too.

I tried running the code above. When I cancelled the process, I got the $tableObj and inspected it in Excel and noted that some entries had been changed, but not all. I was expecting this process to complete rather quickly.

Answer 1

Your code is slow for 2 main reasons, you're doing linear lookups using Where-Object which by itself is slow (this is the slowest technique to filter a collection in PowerShell) but in addition to this, you're doing this linear lookup 2 times per loop iteration when it could be just once:

# lookup just once:
$lookUp = $directoryTable | Where-Object { $_.employeeid -eq $changeRecordLine.employeeID }
# then Name and DN are available to you:
$lookUp.Name
$lookUp.DistinguishedName

What you should use instead of a linear lookup is a structure meant specifically for fast lookups:

$tableObj = Import-Csv ".\employeeIDsAndAttributes.csv"
$map = @{}

# `name, samaccountname, distinguishedname` are already default properties
# no need to include them in `-Properties`
foreach($user in Get-ADUser -Filter "EmployeeId -like '*'" -Properties employeeid) {
    $map[$user.employeeid] = $user
}

foreach($changeRecordLine in $tableObj) {
    $value = $map[$changeRecordLine.employeeid]
    $props = $changeRecordLine.PSObject.Properties

    $parentDN = try {
        $changeRecordLine.DN.SubString($changeRecordLine.Name.Length + 4)
    }
    catch { }

    # `Add-Member` adds overhead even though this is the least of your code's issues,
    # adding `NoteProperties` to your objects by accessing
    # the object's PSObject Properties and adding them manually is faster
    $props.Add([psnoteproperty]::new('Name', $value.Name))
    $props.Add([psnoteproperty]::new('DN', $value.DistinguishedName))
    $props.Add([psnoteproperty]::new('ParentDN', $parentDN))
}