SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Array Element by Agonizing Array Element

By Phil Factor,

Today we have a guest editorial from Phil Factor as Steve is out of the office.

If you are using PowerShell, you’ll often wonder why it is that one uses strange devices such as pipes to process arrays. Anyone who has been weaned on procedural languages such as Java, Basic, C or C# will reach for an iterative loop without thinking too hard. This is because a compiled language is extraordinarily fast at iterating. Anyone who knows what RBAR is will know that SQL being interpreted, is pretty poor at anything that requires iteration. PowerShell, being essentially an interpreted scripting language, is very poor at the iterative loop as well. By ‘very poor’, we are talking about ten minutes in PowerShell as opposed to six seconds in C#.

Why is this? If a loop iterates more than sixteen times, the code of the loop then is compiled dynamically as .NET code and the dynamic method is then invoked inside the loop. Unfortunately, .NET needs to run a security check on the stack, which slows the loop down.

There are four ways I know of by which you can cope with this. You can sulk, and go back to using C# or Python, you can create compiled PowerShell functions that use C#, you can use LINQ functions in PowerShell, or you can use the native language constructs that PowerShell provides, such as the pipe.

The pipe is not entirely pain-free. It is always best to filter as much of the data as you can at the start of the pipeline. However, it is an obvious idiom that is familiar to ops guys who are used to DOS and Bash scripting. LINQ functions can also be very handy for array-handling tasks.

PowerShell and SQL are similar in that, if you use them the way that they are intended, they are fast and efficient, but if you can’t change the coding patterns that you learned from BASIC, Java or C, you will wonder at how slow they are. It is always possible to improve the performance of iterative code: Firstly, use the measure-command  cmdlet to get a feel for what works best. Then make sure that there isn’t a better way that is provided to do the task. For example, initialising an array to a zero, you can try …

$a = ,0 * $length 


 $a = @(); for ($i = 0; $i -lt $length; $i++) { $a += 0 }

.. and you will be amazed by the difference in execution time simply by avoiding having to iterate in PowerShell

Total article views: 90 | Views in the last 30 days: 1
Related Articles

Natively compiled user-defined functions

One new thing that SQL Server 2016 has added is the ability to natively compile user-defined functio...


Faking Multidimensional Arrays in T-SQL made easy!

Another method to create arrays in T-SQL which is actually a "throwback" to the earlier days of comp...


Gotcha! PowerShell Array Initialization, Difference between PowerShell 2.0 and 4.0

I like to initialize my arrays by strongly typing my variable as an array, and setting it to null. P...


Sqlserver compilation error when calling a function

compilation error when calling a function in vbscript


Pass integer array to Code function

How to pass a mutli-valued integer parameter as an array to a VB function