Introduction
After digesting some recent experiences dealing with performance issues using PowerShell and VBScript, I’ve been running some other tests as well. If you aren’t familiar with that one, it was published on this blog and involved COM InterOp and Excel spreadsheets. This led to a very helpful recommendation from Lee Holmes to try using PowerShell v5 (WMF5) “preview”.
As far as the outcome of that previous testing is concerned, Lee was correct. The COM InterOp improvements in the preview build effectively removed any performance variances compared with VBScript. But I deal with a lot more systems and data interfaces than just Microsoft Excel (actually, I almost never have to deal with Excel but in that one case). Among the other scenarios I work with are LDAP and ADSI operations, SQL operations, and WMI/WBEM and WQL query operations. I’ve already tested text file loading and writing (txt, CSV), as well as XML and PS5 is simply fantastic.
Why this test using WQL? I choose the tests because they fall directly within the things I work with on one of my current projects. This project involves a web application which integrates, manages and reports on environment conditions using Microsoft System Center Configuration Manager 2012 R2, Active Directory (Windows Server 2012 R2 forest level), several Solarwinds modules, Oracle Insite services, SQL Server 2012 and all sorts of duct tape, glue, chewing gum and so on. There’s much more to it, but that’s enough blabbering for now.
In any case, one of tasks within this project is invoking PowerShell operations from triggered events. Some of the tasks are still using VBscript due to performance concerns with regards to PowerShell v4, however, those will likely be migrated soon to PowerShell v5 once the WMF release is known. The remaining issues are ADSI/LDAP performance and WMI/WQL performance.
Testing Platforms
The computer used for testing is an HP Z220 workstation with 32 GB of RAM, a Crucial 256 GB M550 SSD system drive, running Windows 8.1 Enterprise Edition 64-bit.
The remote host server is the Configuration Manager primary site server. It’s virtualized on Microsoft Hyper-V on Windows Server 2012 R2. It has been allocated 16 GB of memory and the guest is running Windows Server 2012 R2 Standard Edition.
The server disk configuration is such that the operating system, SCCM, SCCM logs, SQL, SQL logs and backups are on separate drives, each mapped to CSV’s on a dedicated SAN. It has SQL Server 2012 R2 installed locally as well as Configuration Manager 2012 R2.
Both the client and server reside within the same WAN segment on just either sides of a Cisco switch. Performance has not typically been a concern.
Test Criteria
The process used for testing was to query for a known collection, determine membership count, and echo (print) the member count and total runtime in seconds. In addition to a simple query, the WQL statement includes a “LIKE” filter expression to return on those members with a name beginning with “DT-“. This is roughly the same as SQL or T-SQL using “WHERE Name LIKE ‘DT-%'” or “WHERE SUBSTRING(Name,1,3)=’DT-‘”.
The Code Snippets
The first code snippet is the PowerShell script. The same snippet was executed during tests with PowerShell v4 and v5 (September 2014 build). Be advised that my WordPress theme defaults play havoc with code blocks. Wrapping is automatic, not explicit, so be careful with unwrapping if you plan to use them for your own tests.
$MP = "SERVER1.contoso.com"
$NS = "Root\SMS\Site_ABC"
function Count-CM-CollectionMembers {
Param(
[parameter(Mandatory = $true)]$CollID
)
$query = "SELECT Name FROM SMS_FullCollectionMembership WHERE CollectionID='$CollID' AND Name LIKE 'DT-%'"
$coll = Get-WmiObject -Namespace $NS -ComputerName $MP -Query $query
$members = $coll.Count
Write-Output $members
}
$t1 = Get-Date
$count = Count-CM-CollectionMembers -CollID ABC00001
$t2 = Get-Date
Write-Host "$count members found"
New-TimeSpan -Start $t1 -End $t2
For both PowerShell version tests, the ISE was used to execute the code via the GUI run feature.
Next is the VBScript example.
time1 = Timer
cm_host = "SERVER1.contoso.com"
cm_site = "Root\SMS\Site_ABC"
query = "SELECT Name FROM SMS_FullCollectionMembership WHERE CollectionID='ABC00001' AND Name LIKE 'DT-%'"
Set objLocator = CreateObject("WbemScripting.SWbemLocator")
Set objWMIService = objLocator.ConnectServer(cm_host, cm_site)
Set colItems = objWMIService.ExecQuery(query,,48)
i = 0
For each objItem in colItems
i = i + 1
Next
wscript.echo i & " members found"
wscript.echo Round(Timer - Time1,2) & _
" seconds"
The Results
Here are the results and graph…
Summary
It’s worth noting that I used a much less efficient method for deriving the membership count in the VBScript code snippet. Rather than dealing with the object collection directly, I ran a dumb iteration counter.
Also, at the end of each script, to indicate the cumulative run times, I use slightly different methods. Again, the VBScript example uses a more cumbersome approach that consumes additional processing cycles (subtracting and rounding).
The net effect was a bit surprising given that it still rendered consistently shorter run times than either PowerShell 4 or 5. The performance did improve from PowerShell v4 to v5 (September 2014 preview build), and almost matches WSH/VBscript results, even after five (5) test cycles. I’m hoping the next preview build (or final release) will continue to add more performance improvements as well as the noted inclusion of DCM features.
Back to my crazy boring life. Already in progress.
Skatterbrainz Blog 