通过powershell更改CSV值的性能增强

Question

请参考前面的问题:如何用id替换(id值从XML中获取)并放入CSV, powershell

我需要添加ForEach-Object而不是ForEach，我有11,000行。所以，它花了太多时间。

foreach ($csvRow in $csvRows) {
foreach ($columnName in $columnNames) {
$id = $csvRow.$columnName
if (-not $id) { continue }
$newId = @($xmlDoc.enum_types.enum_type.Where( { $_.field_name -eq $columnName }, 'First').
items).ForEach( { $_.item }).Where( { $_.id -eq $id }).value
$csvRow.$columnName = $newId
}
}
$csvRows | Export-Csv -NoTypeInformation -Encoding utf8 $CSVpath

Answer 1

您可以做的一件事是从"枚举"中预先计算查找。XML

原始代码- 7649毫秒

# setup
$xml = @"
<enum_types>
<enum_type field_name="Test1">
<items>
<item>
<id>1</id>
<value>A</value>
</item>
</items>
</enum_type>
<enum_type field_name="Test2">
<items>
<item>
<id>1</id>
<value>A</value>
</item>
</items>
</enum_type>
</enum_types>
"@;
$enums = [xml] $xml;
$csv = @("Test1, Test2");
$csv += 1..110000 | % { "1, 1" }
$data = $csv | ConvertFrom-Csv
$columnNames = @( "Test1", "Test2" );
# perf test - 7649 milliseconds
measure-command -expression {
foreach ($csvRow in $data)
{
foreach ($columnName in $columnNames)
{
$id = $csvRow.$columnName
if ( -not $id )
{
continue;
}
$newId = @($enums.enum_types.enum_type.Where( { $_.field_name -eq $columnName }, 'First').
items).ForEach( { $_.item }).Where( { $_.id -eq $id }).value
$csvRow.$columnName = $newId
}
}
}

使用预先计算的查找- 792毫秒

# setup 
... as above ...
# pre-compute lookups
$lookups = @{};
foreach( $enum_type in $enums.enum_types.enum_type )
{
$lookups[$enum_type.field_name] = @{}
foreach( $item in $enum_type.items.item )
{
$lookups[$enum_type.field_name][$item.id] = $item.value
}
}
write-host ($lookups | convertto-json -depth 99)
# {
#   "Test1": {
#     "1": "A"
#   },
#   "Test2": {
#     "1": "A"
#   }
# }
# perf test - 792 milliseconds
measure-command -expression {
foreach ($csvRow in $data)
{
foreach ($columnName in $columnNames)
{
$id = $csvRow.$columnName
if (-not $id)
{
continue;
}
$csvRow.$columnName = $lookups[$columnName][$id]
}
}
}

你可以从这个方法中挤出更多的东西，但它已经是10倍的加速了(在我非常有限的基准测试中)。

Answer 2

预加载所有新条目到哈希表:

$Types = $xmlDoc.enum_types.enum_type
$HashTable = @{}
Import-Csv .1.Csv |ForEach-Object { $Names = $Null } {
if (!$Names) {
$Names = $_.psobject.Properties.Name
foreach ($Name in $Names) {
$HashTable[$Name] = @{}
foreach ($Field in $Types.Where{$_.field_name -eq $Name}) {     
foreach ($Item in $Field.Items.Item) {
$HashTable[$Name][$Item.Id] = $Item.Value
}
}
}
}
foreach ($Name in $Names) {
$id = $_.$Name
If ($HashTable[$Name].Contains($Id)) {
$_.$Name = $HashTable[$Name][$Id]
}
}
$_
} |Export-Csv .1New.Csv -NoTypeInformation -Encoding utf8