Recently I was working with a 60,000 row CSV and applying several filters to find matching rows.   This took between 1.0-1.8 seconds per row.   While OK in small scale (few hundred) the script run duration grew to several hours.    There had to be a better way.

Enter [System.Data.DataTable]

Native PowerShell collections are simple to create and easy to work with.   However, in large scale I find DataTable to perform better.   It requires more up front coding to get ready (columns, schema, populate rows) but then runs crazy fast.   Sample code below.

Enjoy! 

PowerShell

# Load CSV
$coll = Import-CSV "people.csv"
# Create table
$dtPeople = New-Object System.Data.DataTable("people")
$cols = @("userlogin","username","managerlogin","managername","department")
# Schema (columns)
foreach ($col in $cols) {
	$dtPeople.Columns.Add($col) | Out-Null
}
# Values (rows)
foreach ($c in $coll) {
	$row = $dtPeople.NewRow()
	foreach ($col in $cols) {
		$row[$col] = $c.$col
	}
	$dtPeople.Rows.Add($row) | Out-Null
}
# Do we have this user?
$findUser	= "TEST1"
# DataView rapid filter
$dvPeople	= New-Object System.Data.DataView($dtPeople)
$dvPeople.RowFilter = "UserLogin = '$findUser'"
# Result
if ($dvPeople.Count -gt 0) {
	Write-Host "Found" -ForegroundColor Yellow
	$dvPeople | ft
} else {
	Write-Host "Not Found!" -ForegroundColor Red
}

People.CSV

"userlogin","username","managerlogin","managername","department"
TEST1,Test Guy1,mgr1,Manager Guy1,IT
TEST2,Test Guy2,mgr1,Manager Guy1,IT