如何用PowerShell 7快速从SQL Server 2005提取关联数据？

我需要从两个视图关联提取数据：从视图vEngBillsOfMaterial_Aras的usedonRn列取值，匹配视图[vEngParts_Master_Aras]的对应数据，合并为数组导出。

当前用PowerShell 7处理SQL Server 2005数据，现有两种方式：逐行更新数组后一次性导出CSV，或逐行写入CSV（当前代码采用后者）。但处理70万行数据速度极慢——5万行耗时12小时。尝试用视图替代CSV，但因数据含派生/常量值无法更新视图。

请问实现该需求的最快方式是什么？

当前代码：

$server = "ServerName"
$database = "DBName"
$csvFilePath = "CSVPath"
$query = "SELECT Distinct UsedOnRn From [Eng].[dbo].[vEngBillsOfMaterial] Order By UsedOnRn ASC"
$invokeQuery = Invoke-Sqlcmd -ServerInstance $server -Database $database -Query $query
$array = @()
foreach($item in $invokeQuery){
    $usedOnRn = $item.UsedOnRn
    $queryParts = "Select Revision,PartRn,Part_Number from [Eng].[dbo].[vEngParts_Master] Where PartRn = '$usedOnRn'"
    $queryUsedOnRn = "SELECT * From [Eng].[dbo].[vEngBillsOfMaterial] Where UsedOnRn = '$usedOnRn'"
    $invokeUsedOnRn = Invoke-Sqlcmd -ServerInstance $server -Database $database -Query  $queryUsedOnRn
    $invokeQueryParts = Invoke-Sqlcmd -ServerInstance $server -Database $database -Query  $queryParts
    Foreach($row in $invokeUsedOnRn){
        $array = [PSCustomObject]@{
            PartRn = $row.PartRn
            PART_NUMBER = $row.PART_NUMBER
            Revision =  $row.Revision
            UsedOnRn =$row.UsedOnRn
            RevOfUsedOnRn = $invokeQueryParts.Revision
            PartNoOfUR = $invokeQueryParts.Part_Number
            Item = $row.Item
            QtyPerAssy = $row.QtyPerAssy
            Remarks = $row.Remarks
        }
        $array | Format-table 
    }
    $array | Export-Csv -Path $csvFilePath -Append -NoTypeInformation -Force 
}

1. 用SQL JOIN一次性完成关联查询 #

把两个视图的关联逻辑放到SQL语句中，让数据库引擎处理关联——数据库的查询优化器能高效处理大表关联，完全避免PowerShell逐行发起查询的网络往返开销，这是性能提升的核心。

示例查询语句：

SELECT 
    bom.PartRn,
    bom.PART_NUMBER,
    bom.Revision,
    bom.UsedOnRn,
    pm.Revision AS RevOfUsedOnRn,
    pm.Part_Number AS PartNoOfUR,
    bom.Item,
    bom.QtyPerAssy,
    bom.Remarks
FROM [Eng].[dbo].[vEngBillsOfMaterial] bom
LEFT JOIN [Eng].[dbo].[vEngParts_Master] pm 
    ON bom.UsedOnRn = pm.PartRn

• 用LEFT JOIN确保即使vEngParts_Master中无匹配PartRn，也能保留vEngBillsOfMaterial的数据；如果只需要两边都匹配的结果，改用INNER JOIN。

2. 优化PowerShell导出逻辑 #

拿到一次性查询的结果后，直接用Export-Csv导出，避免逐行追加（-Append会反复打开/关闭文件，IO开销极大）。

优化后的PowerShell代码：

$server = "ServerName"
$database = "DBName"
$csvFilePath = "CSVPath"

# 一次性关联查询的SQL语句
$query = @"
SELECT 
    bom.PartRn,
    bom.PART_NUMBER,
    bom.Revision,
    bom.UsedOnRn,
    pm.Revision AS RevOfUsedOnRn,
    pm.Part_Number AS PartNoOfUR,
    bom.Item,
    bom.QtyPerAssy,
    bom.Remarks
FROM [Eng].[dbo].[vEngBillsOfMaterial] bom
LEFT JOIN [Eng].[dbo].[vEngParts_Master] pm 
    ON bom.UsedOnRn = pm.PartRn
"@

# 一次性获取所有数据并导出
Invoke-Sqlcmd -ServerInstance $server -Database $database -Query $query | 
    Export-Csv -Path $csvFilePath -NoTypeInformation -Force

3. 超大数据量的流式处理（可选） #

如果数据量超过内存承载上限，用SqlDataReader流式读取并写入CSV，避免一次性加载所有数据到内存：

$connectionString = "Server=$server;Database=$database;Integrated Security=True"
$connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
$command = $connection.CreateCommand()
$command.CommandText = $query

$connection.Open()
$reader = $command.ExecuteReader()

# 创建CSV写入器
$writer = New-Object System.IO.StreamWriter($csvFilePath)
# 写入表头
$headers = (0..($reader.FieldCount-1) | ForEach-Object { $reader.GetName($_) }) -join ','
$writer.WriteLine($headers)

# 逐行写入数据，处理含特殊字符的字段
while ($reader.Read()) {
    $row = (0..($reader.FieldCount-1) | ForEach-Object { 
        $value = $reader.GetValue($_)
        if ($value -match '[,"]') {
            "`"$($value.ToString().Replace('"', '""'))`""
        } else {
            $value.ToString()
        }
    }) -join ','
    $writer.WriteLine($row)
}

# 清理资源
$writer.Close()
$reader.Close()
$connection.Close()

原代码慢的核心原因 #

• 多次SQL往返：循环70万次，每次发起2次SQL查询，总共140万次网络请求，这是最大性能瓶颈。
• 逐行追加CSV：Export-Csv -Append反复操作文件，IO开销极大。
• 冗余操作：Format-Table仅用于控制台输出，完全没必要，额外消耗性能。

内容的提问来源于stack exchange，提问作者Brute