如何用PowerShell高效将Mongo导出的JSON导入SQL Server表？

场景与问题背景 #

需要用PowerShell将Mongo的Export-MdbcData工具导出的JSON文件导入SQL Server表，示例JSON为每行一条独立对象（JSON Lines格式）：

{ "code" : "0088", "name" : "BUTTON", "detail" : { "quantity" : 1 } }
{ "code" : "0081", "name" : "MATTERHORN", "detail" : { "quantity" : 2 } }
{ "code" : "0159", "name" : "BANKSTON", "detail" : { "quantity" : 1 } }

初始脚本采用读入数组转DataTable的方式，小文件导入仅需几秒，但处理400万+条记录时耗时数小时，急需更高效的JSON读取解析方案。

已做优化及现状 #

采纳建议修改脚本，移除内部foreach循环后，400万+条记录导入耗时降至约17分钟（批量大小设为80K，每次插入约14秒），但同规模CSV文件每次插入仅需3秒，推测JSON解析的开销远高于分隔符格式。修改后的脚本如下：

foreach ($line in [System.IO.File]::ReadLines($pathToJsonFile, $encoding)) 
{
    $json = $line | ConvertFrom-Json;
    [void]$dataTable.Rows.Add($json.code, $json.name, $json.detail.quantity);
    $i++; 
    if (($i % $batchsize) -eq 0) { 
        $bulkcopy.WriteToServer($dataTable) 
        Write-Host "$i rows have been inserted in $($elapsed.Elapsed.ToString())."
        $datatable.Clear() 
    }
}

进一步优化方案 #

针对JSON解析性能瓶颈，可从以下几个方向优化：

1. 替换内置ConvertFrom-Json为高效解析库 #

PowerShell内置的ConvertFrom-Json基于旧版.NET解析器，性能有限。推荐使用System.Text.Json（.NET Core/.NET 5+自带，Windows PowerShell需手动导入），其解析速度比内置命令提升数倍：

# 导入System.Text.Json程序集（Windows PowerShell需执行，PowerShell Core可省略）
Add-Type -AssemblyName System.Text.Json

# 预定义DataTable结构，避免动态类型开销
$dataTable = New-Object System.Data.DataTable
$dataTable.Columns.Add("code", [string]) | Out-Null
$dataTable.Columns.Add("name", [string]) | Out-Null
$dataTable.Columns.Add("quantity", [int]) | Out-Null

$i = 0
$batchsize = 80000
$elapsed = [System.Diagnostics.Stopwatch]::StartNew()

foreach ($line in [System.IO.File]::ReadLines($pathToJsonFile, $encoding)) {
    # 使用System.Text.Json解析单行JSON，直接取值避免动态对象开销
    $jsonDoc = [System.Text.Json.JsonDocument]::Parse($line)
    $code = $jsonDoc.RootElement.GetProperty("code").GetString()
    $name = $jsonDoc.RootElement.GetProperty("name").GetString()
    $quantity = $jsonDoc.RootElement.GetProperty("detail").GetProperty("quantity").GetInt32()
    
    [void]$dataTable.Rows.Add($code, $name, $quantity)
    $i++

    if (($i % $batchsize) -eq 0) {
        $bulkcopy.WriteToServer($dataTable)
        Write-Host "$i rows inserted in $($elapsed.Elapsed.ToString())"
        $dataTable.Clear()
        $jsonDoc.Dispose() # 手动释放资源，避免内存泄漏
    }
}

# 处理剩余未批量提交的记录
if ($dataTable.Rows.Count -gt 0) {
    $bulkcopy.WriteToServer($dataTable)
    Write-Host "Total $i rows inserted in $($elapsed.Elapsed.ToString())"
}
$elapsed.Stop()

2. 批量读取+批量解析 #

一次性读取多行JSON（比如一次读取10000行），减少IO操作的频次，进一步降低开销：

$reader = [System.IO.StreamReader]::new($pathToJsonFile, $encoding)
$parseBatchSize = 10000
$batchLines = @()

while (!$reader.EndOfStream) {
    $batchLines += $reader.ReadLine()
    if ($batchLines.Count -ge $parseBatchSize) {
        foreach ($line in $batchLines) {
            # 沿用上述System.Text.Json解析逻辑
            $jsonDoc = [System.Text.Json.JsonDocument]::Parse($line)
            # ... 取值并添加到DataTable
            $jsonDoc.Dispose()
        }
        $batchLines = @()
    }
}

# 处理剩余行
foreach ($line in $batchLines) {
    # 解析逻辑同上
}
$reader.Close()
$reader.Dispose()

3. 调整批量插入的最优大小 #

当前80K的批量大小可能并非最优值，可测试50K、100K等不同批量，找到SQL Server SqlBulkCopy的平衡点——过大的批量会占用过多内存，过小则会增加数据库交互次数。

4. 提前禁用DataTable的约束检查 #

在批量添加行之前，禁用DataTable的约束和索引检查，能减少插入时的验证开销：

$dataTable.EnforceConstraints = $false
# 批量添加行逻辑...
$dataTable.EnforceConstraints = $true

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