Tuesday, January 24, 2012

PowerShell - Let SQL sort it out

I came across a piece of code yesterday that provided a learning opportunity. The code was a simple SQL query that returned a list of computers from a database. The code I saw, had PowerShell handling the sort after the computers were retrieved from the SQL database. While this works, it is not a best practise. In fact, it something that Don Jones has often mentioned - Filter Left, Format Right. Look below to see the performance difference of letting SQL Server handle the sort.
"Filter Left: {0} seconds" -f (Measure-Command -Expression {           
$qry = "select name from vcomputer where [IsManaged] ='1' order by name"
$Altiris = Invoke-Sqlcmd -ServerInstance SQL01 -Database Altiris -Query $qry

"Filter Right: {0} seconds" -f (Measure-Command -Expression {
$qry = "select name from vcomputer where [IsManaged] ='1'"
$Altiris = Invoke-Sqlcmd -ServerInstance SQL01 -Database Altiris -Query $qry | sort

As you can see, this is a significant difference!
Like Active Directory, let the server that is good at filtering or sorting handle the work for you.


No comments: