Speed up array searches?

Ask your PowerShell-related questions, including questions on cmdlet development!
Forum rules
Do not post any licensing information in this forum.

Any code longer than three lines should be added as code using the 'Select Code' dropdown menu or attached as a file.
Post Reply
ClipperXTP
Posts: 27
Joined: Tue Apr 28, 2020 4:07 am

Speed up array searches?

Post by ClipperXTP »

Hi

I am running a powershell query against SQL database to pull back computer name and model for about 6,000 computers

$adapter = new-object system.data.sqlclient.sqldataadapter ($query, $conn)
$table = new-object system.data.datatable
$adapter.Fill($table) | out-null

$compArray = @($table)

-------------------------------------------


I then wish to query $compArray for the model of an individual computer $computername at a later point.

I am doing:
$model = ($compArray | ?{ $_.computer -eq $computername }).model

It works but it takes around 6 or 7 seconds. I have bags of ram and processing power. I have tried sorting $compArray by computer but it hasn't made it any faster.

$compArray | gm gives:
TypeName: System.Data.DataRow

Is there any way to index the array / table so that I can search it quicker? Or convert it to a different type of object?

thanks in advance

jvierra
Posts: 14452
Joined: Tue May 22, 2007 9:57 am
Contact:

Re: Speed up array searches?

Post by jvierra »

There is no way to add an index to an array. You could convert the table into a keyed collection but that only allows one key. A table can also use a "RowFilter" to restrict the rows.

If you are always looking for a specific single computer then querying the database for that computer would likely be faster.

For a table query to be efficient the code must be written correctly for the situation. Using an adapter is the first thing that would need t go if you are not planning on updating the database as adapters are dynamic and used to do data editing. This makes them a bit slower and harder to use.

jvierra
Posts: 14452
Joined: Tue May 22, 2007 9:57 am
Contact:

Re: Speed up array searches?

Post by jvierra »

Here is an example of how to query into a table object and how to access that object correctly. I just ran this against a table which returns 13000 rows. It is instantaneous.

Code: Select all

function Get-SQLTable{
    param(
        $Sql,
        $Database = 'master',
        $Server
    )
    Try{
        $conn = [system.Data.SqlClient.SqlConnection]::new("server=$Server;database=$Database;Integrated Security=SSPI;")
        $conn.open()
        $cmd = $conn.CreateCommand()
        $cmd.CommandText = $Sql
        $rdr = $cmd.ExecuteReader()
        $dt = [System.Data.DataTable]::new()
        $dt.Load($rdr)
        $conn.Close()
        return (,$dt)
    }
    Catch{
        Throw $_
    }
}
$server = 'omega\sqlexpress'
$dt = Get-SQLTable -Sql 'select * from Syscolumns' -Server $server

# filter using array capabilities provided by Linq
$dt.Rows.Where({$_.name -eq 'status'})

# filter using "RowFilter" to restrict rows.
$dt.DefaultView.RowFilter = "name='status'"
$dt.DefaultView  # returns filtered rows.
The Sql will work on any SQLServer edition. It is generic just change the instance name.

ClipperXTP
Posts: 27
Joined: Tue Apr 28, 2020 4:07 am

Re: Speed up array searches?

Post by ClipperXTP »

This is excellent, thanks very much

Post Reply