Using -comObject in pwsh7 is extremly slow compaired to powershell 5.1

[skippernl]

Hi I was pointed in this direction by the pwsh7 team.

I have a powershell script that takes the config of a firewall and creates an excel document from it.
On powershell 5.1 this takes about 5 minutes depending on the size of the config.
On powershell 7 this takes more that 2 hours.
The main function that slows down is writing to excel
$Excel = New-Object -ComObject Excel.Application
$Excel.Visible = $false
$workbook = $excel.Workbooks.Add()
$TocSheet = $workbook.Worksheets.Item(1)
$MainSheet = $workbook.Worksheets.Add()
$TocSheet.Name = "ToC"

Commenting the firstline out (The new-object)
Makes the script run on pwsh7 in 49 seconds to 2 minutes and 35 seconds in powershell 5.

As the main function of this script is converting a config file to excel tabs it is higly depending on the -ComObject.

Full script can be found here
https://github.com/skippernl/Fortigate2Excel

[Dotnet-GitSync-Bot]

I couldn't figure out the best area label to add to this issue. Please help me learn by adding exactly one area label.

[janvorli]

cc: @AaronRobinsonMSFT

[AaronRobinsonMSFT]

@skippernl Thanks for reaching out. It would be helpful if you could collect a ETW trace using PerfView. This will allow us to understand your scenario better and observe precisely what aspect of the script is taking so long. Two hours would result in a large file, but we can probably get away with collecting data for no more than 30 to 60 seconds. A trace for pwsh5.1 would also be helpful, but we can start with pwsh7.

[AaronRobinsonMSFT]

/cc @jkoritzinsky @elinor-fung

[skippernl]

Will try that tomorrow. Thanx Op ma 25 mei 2020 18:50 schreef Aaron Robinson <notifications@github.com>:

…

@skippernl <https://github.com/skippernl> Thanks for reaching out. It would be helpful if you could collect a ETW trace using PerfView <https://github.com/microsoft/perfview>. This will allow us to understand your scenario better and observe precisely what aspect of the script is taking so long. Two hours would result in a large file, but we can probably get away with collecting data for no more than 30 to 60 seconds. A trace for pwsh5.1 would also be helpful, but we can start with pwsh7. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#36944 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ABIWZN5MHCIHLBTFTSTDHZDRTKOUVANCNFSM4NIZ24BA> .

[skippernl]

Ran both. https://1drv.ms/u/s!AikuTJDzUoogh8Y6FT2qYZfu43LWIw?e=Z3Bjz8 [image: afbeelding.png] You should be able to download the files from the onedrive location. If needed I can add a test file for a fortigate config file so you can test on your machine. The configuration is just the interfaces and a routing table. Skippernl Op ma 25 mei 2020 om 18:50 schreef Aaron Robinson <notifications@github.com

…

: @skippernl <https://github.com/skippernl> Thanks for reaching out. It would be helpful if you could collect a ETW trace using PerfView <https://github.com/microsoft/perfview>. This will allow us to understand your scenario better and observe precisely what aspect of the script is taking so long. Two hours would result in a large file, but we can probably get away with collecting data for no more than 30 to 60 seconds. A trace for pwsh5.1 would also be helpful, but we can start with pwsh7. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#36944 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ABIWZN5MHCIHLBTFTSTDHZDRTKOUVANCNFSM4NIZ24BA> .

-- Xander

[AaronRobinsonMSFT]

@skippernl Thank you much for the collected traces. The traces identified the issue very quickly. Unfortunately, this appears to be an issue in PowerShell. The slow down is due to code paths in System.Management.Automation and from the trace appears to be because the code path when run on .NET Core falls back to expression tree evaluation instead of directly making the COM calls. I am not a PowerShell user nor have any experience with it as a developer so don't know why this code path is being taken, but it is because of this expression tree usage that the call is so expensive.

We can leave this issue open here since the runtime may need to respond or help the PowerShell run time. However, we need someone from PowerShell to interpret the trace on why this is occurring. Can you tag the person you were talking to?

[skippernl]

Sure, Steve Lee pointed me in you're direction. SkipperNL Op di 26 mei 2020 19:42 schreef Aaron Robinson <notifications@github.com>:

…

@skippernl <https://github.com/skippernl> Thank you much for the collected traces. The traces identified the issue very quickly. Unfortunately, this appears to be an issue in PowerShell. The slow down is due to code paths in System.Management.Automation <https://docs.microsoft.com/dotnet/api/system.management.automation> and from the trace appears to be because the code path when run on .NET Core falls back to expression tree evaluation instead of directly making the COM calls. I am not a PowerShell user nor have any experience with it as a developer so don't know why this code path is being taken, but it is because of this expression tree usage that the call is so expensive. We can leave this issue open here since the runtime may need to respond or help the PowerShell run time. However, we need someone from PowerShell to interpret the trace on why this is occurring. Can you tag the person you were talking to? — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#36944 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ABIWZN5MGEOTJJTW5Q45YTDRTP5R3ANCNFSM4NIZ24BA> .

[skippernl]

@SteveL-MSFT Can you or a college chime in?

[skippernl]

Hi any progress on this issue? If needed I can produce a small config file for testing.

[daxian-dbw]

@skippernl I'm from the PowerShell team and is investigating this issue.
A small config file would absolutely be helpful. Can you please confirm the repro step is .\Fortigate2Excel.ps1 -fortiGateConfig "<your-small-config-file>" on both PS7 and PS5.1?