3
\$\begingroup\$

I am trying to make my script run a lot faster. I have a csv that has about 330k of lines on it that I plan on running my script with and it has been taking an extremely long time. I just did ran the script and it 50 mins to generate the new csv. What the code does is it adds 2 more fields and filters only the administrators and The windows operating systems. Below are before and after pictures of the output.

Input Output

$csv = Import-Csv 'U:\Local Group Members.csv' |
Where-Object {($_.Name0 -eq "administrators") -and ($_.caption0 -match "Microsoft Windows 10 Enterprise|Microsoft Windows 7 Enterprise|Microsoft Windows 7 Professional|Microsoft Windows 8 Enterprise|Microsoft Windows 8 Pro|Microsoft Windows 8.1 Enterprise|Microsoft Windows 8.1 Pro")} |
Select-Object "Netbios_name0", "Name0", "Account0","category0","Domain0","Unique Account Name","Type0","caption0", "Excluded"
#Modify each line
Foreach ($row in $csv) {
If ($row.Type0 -eq 'Domain') { 
    $row."Unique Account Name" = "$($row.Domain0) - $($row.Account0)" 
    If ($row."Unique Account Name" -in @('ACCOUNTS - DODSCAN'.'ACCOUNTS - Domain Admins','ACCOUNTS - LADM_WS_Admins','ACCOUNTS - Tech Enterprise'))
         {$row."Excluded" = "True"}
    Else {$row."Excluded" = "False"}        
}
Else {
    $row."Unique Account Name" = "$($row.Netbios_name0) - $($row.Account0)"
    If ($row."Account0" -in @('esrxadm1n_esi','#Update','medco_tech','medco_admin'))
         {$row."Excluded" = "True"}
    Else {$row."Excluded" = "False"}
}
    Write-Host $row."Unique Account Name"
    Write-Host $row."Excluded"    
}

#Export CSV
$csv | Export-Csv U:\$(get-date -f yyyy-MM-dd-hh-mm)-TestOutput1.csv -NoTypeInformation 

Can someone help me make my script faster?

\$\endgroup\$
  • \$\begingroup\$ How long is an extremely long time? It'd be nice if you also added a sample of your CSV and told us what you are doing with it. \$\endgroup\$ – t3chb0t Jun 15 '17 at 16:56
  • \$\begingroup\$ @t3chb0t I just revised it, it took about 50 mins to generate the csv. The original file is about 33mb. I added pictures as well. \$\endgroup\$ – MountaindewKing Jun 15 '17 at 17:57
  • \$\begingroup\$ Ok, 50min for only 330k rows and a 30mb large file is IMO really way too much. Can you explain what the filtering is about? I'm pretty sure this is the bottleneck. \$\endgroup\$ – t3chb0t Jun 15 '17 at 18:00
  • \$\begingroup\$ @t3chb0t In the Name0 column we just want the Administrators so we are filtering just for those. The file comes with a bunch of other names in the Name0 column so we are filtering those first. Then we are also filtering the operating systems that we need, which are provided above. Otherwise they also have servers and we just need the operating systems. \$\endgroup\$ – MountaindewKing Jun 15 '17 at 18:06
  • 1
    \$\begingroup\$ Take a look at this Stack Overflow question and answers: stackoverflow.com/questions/4780305/… These are methods you can use to "profile" your code to determine what is the slowest part. \$\endgroup\$ – John Deters Jun 15 '17 at 18:53
1
\$\begingroup\$

As far as performance goes there are a few places to make changes.

Make better use of the pipeline

You read in the csv to a variable then process. Convert the script to process these records directly. There would be performance increases this way. Insanely brief example...

Import-Csv 'U:\Local Group Members.csv' |
   Where-Object {($_.Name0 -eq "administrators")}
   Select-Object "Netbios_name0", "Name0" | 
   ForEach-Object {
       # Something happens. 
} | Export-Csv ....

Wasted console output

I cannot imagine you are checking ~1000's of lines of console output. You are wasting processing time but putting that data to console. Remove the write-host lines. Even if you have this running as a task.

Where-Object is slow

This is really easy to use but is not very performance friendly. I can tell you have at least PowerShell 3.0 but if you have at least v4 then you have access to the where and foreach methods of collections. The syntax as you will see is very similar but there should be performance boons here as well. Example:

(Import-Csv -Path C:\temp\devices.csv).where({$_.DeviceType -ne "BlackBerry"})

I think you are already doing this but order your conditions in order of least likely to most likely. PowerShell will not evaluate the RHS of -and if the LHS is already false.


I tried to improve the logic in your regex but it was at a cost of performance everytime. As redundant as that string is it seems to be a faster choice given other options.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.