Powershell Merge 2 lists Performance

Question

I have two lists in Powershell with a large amount of data:

$Byods containing MACs and Usernames with approximately 5000 items
$DHCPLeases containing MACs and IPs with approximately 3000 items

I want to create a new list containing Usernames and IPs where the Byods list is leading, and the IPs are found from the DHCPleases, only containg records that found a match (a left join?)

I created a foreach loop, that does the job. However, its taking a huge ammount of time to complete (> 30 min).

Im sure this can be faster. Anyone?

$UserByods = @()
foreach ($lease in $DHCPLeases) 
{
    $MAC = [string]$lease.MAC
    $UserByod = @()
    $UserByod = $Byods | where {$_.MAC -eq $MAC}
    if (($UserByod | measure).count -eq 1) {
        $ByodIP = New-Object -TypeName PSObject
        $ByodIP | Add-Member -Name 'User' -MemberType Noteproperty -Value $UserByod.Username
        $ByodIP | Add-Member -Name 'IP' -MemberType Noteproperty -Value $lease.IP
        $UserByods += $ByodIP
    }
}

To start : replace the $userbyods += with something like a list.add() (first create a new list). using += makes a copy of the array on each iteration. Second : you can put steps in between with a stopwatch to check performance and see which steps take a "long" time to run. — bluuf, Sep 07 '17 at 09:14

score 4 · Answer 1 · answered Sep 07 '17 at 09:41

Appending to an array in a loop is slow. Just output your custom objects in the loop and collect the loop output in the variable $UserByods. Linear reads on a list are slow as well. Better build a hashtable from $Byods so you can lookup devices by their MAC address.

$tbl = @{}
$Byods | ForEach-Object { $tbl[$_.MAC] = $_ }

$UserByods = foreach ($lease in $DHCPLeases) {
    New-Object -TypeName PSObject -Property @{
        'User' = $tbl[$lease.MAC].Username
        'IP'   = $lease.IP
    }
}

Whow, thank you both so much! This reduced the script to just 2 seconds! — Sjoerdvw, Sep 07 '17 at 13:22

score 4 · Accepted Answer · answered Sep 07 '17 at 09:41

A number of improvements can be made here. First off, don't use Add-Member to construct the objects, it'll be significantly slower than specifying the properties up front.

You'd also want to avoid using the addition operator (+=) on an collection, since it'll cause the underlying array to be resized which is a quite memory-intensive operation.

Finally use a hashtable for the MAC correlation, it'll be much faster than looping through all 5000 entries 3000 times (which is what ...| Where {...} basically does):

$BYODTable = $Byods |ForEach-Object -Begin { $table = @{} } -Process { $table[$_.MAC] = $_.Username } -End { return $table }

$UserByods = foreach ($lease in $DHCPLeases) 
{
    $MAC = [string]$lease.MAC
    if ($BYODTable.ContainsKey($MAC)) {
        New-Object -TypeName PSObject -Property @{
            User = $BYODTable[$MAC]
            IP   = $lease.IP
        }
    }
}

iRon · Answer 3 · 2017-09-07T17:50:01.573

0

I do not have your list but I wonder how my Join-Object cmdlet performs on this.
The command should be something like this:

$Byods | LeftJoin $DHCPLeases Mac

I have put quiet some effort in the performance but as it is a general solution it might not be able to compete with the specific solution given here...

edited Sep 07 '17 at 17:50

answered Sep 07 '17 at 15:02

iRon

20,463
10
53
79

Powershell Merge 2 lists Performance

3 Answers3

Linked