Speed up array searches?

[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]

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]

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]

This is excellent, thanks very much