Searching through large Powershell arrays

Question

Warning: Powershell novice, terms/code may be incorrect

Say I have a Powershell array with data from Invoke-Sqlcmd, say 10,000 "rows" with a half dozen (Col1-Col6) columns each. Say I want to find every row with an empty Col5. Right now I'm doing

foreach ($row in $sqlimport.col1)
 { $col5data = $sqlimport.Where({$_.col1 -eq $row}) | Select-Object -ExpandProperty Col5
if ( '' -eq $col5data ) {$col5data+1} else {} $col5data

Returns 1, which it should in my test. Code seems wrong and slow. It takes several minutes to run through. When something like

$sqlimport.Where({$_.col5 -eq 'somedatathatisthere'})

takes milliseconds

However,

$sqlimport.Where({$_.col5 -eq ''})

Returns blank

($sqlimport.Where({$_.col5 -eq ''})).Count

Returns 0

Answer 1

Right now, you're asking PowerShell to create an array consisting of all the values in column col1, and then for each iteration you search the entire array again to find the corresponding col5 value. That's entirely unnecessary.

Simply loop over the array itself:

foreach($row in $sqlimport){
    if($row.Col5 -like ''){
        Write-Host "Col5 is empty in row with id $($row.Col1)"
    }
}

This only iterates over the entire array once.

Answer 2

Mathias' answer explains the problem with the inefficiency of your first attempt well and offers a solution that ultimately performs best, due to use of a (single) foreach statement (as opposed to the much slower ForEach-Object cmdlet).

However, a solution using a (single) .Where() array method call (or the related .ForEach() method):

• is only slightly slower^[1]
• while being more concise and arguably conceptually more elegant.

It is what you tried with $sqlimport.Where({ $_.col5 -eq '' }), which, based on your feedback, requires only one small tweak to make it work:

• Instead of -eq '' use -like '', which is a somewhat obscure^[2] shortcut to testing for an empty string ('') and also for a $null value (as also used in Mathias' answer) - it is, in essence, the equivalent of .NET's [string]::IsNullOrEmpty() method.

# -like matches both '' and $null
$sqlimport.Where({ $_.col5 -like '' })

Note:

• If you wanted to test for $null values only, use the following; note how $null is placed on the LHS, which is generally advisable for robust comparisons^[3]:

  $sqlimport.Where({ $null -eq $_.col5 })
  

  • As an aside: GitHub issue #10656 proposes introducing a simplified test for $null with something like -is $null; unfortunately, the associated PR has been abandonded.

• As an aside: [string]-type-constrained PowerShell variables never store $null values:
  [string] $str = $null causes $str to contain '', not $null. However, [string]-typed property values not populated in PowerShell code can contain $null values, as in your case.

  • For passing a true $null value to a string-typed .NET API in PowerShell code, a special singleton must be used (passing $null directly would again result in ''): [NullString]::Value`
  • See this answer for background information.

---

^{[1] For a performance comparison of PowerShell's various enumeration (iteration) features, see the bottom section of this answer.}

^{[2] The real purpose of the -like operator is to perform wildcard-expression matching. The shortcut relies on
-like - which operates on (non-null) strings only - auto-converting non-string operands to strings, which in the case of $null causes conversion to '' (the empty string).}

^{[3] To reliably test for $null, place it on the LHS of an -eq / -ne operation; e.g., $null -eq $var. If you place $null on the RHS - $var -eq $null - and $var happens to be a collection (such as an array), the return value is the array of matching elements, i.e. the array of those elements in $var whose value is $null, which is a different operation - see about_Comparison_Operators.}