如何优化API调用、遍历结果插入SQL的执行速度？

当前PowerShell脚本因嵌套循环、固定3秒的Start-Sleep（应对API限流）导致全流程耗时数小时，需针对API调用→结果遍历→SQL插入全链路提供提速改进方案。

当前脚本 #

$token = Invoke-RestMethod -Uri https://<uri path> -Body $body -Method Post -UseBasicParsing

$header = @{Authorization='Bearer '+$token.access_token}

$dataGroup1 = Invoke-RestMethod -Header $header -Uri https://<pathname> -UseBasicParsing

$add = For ($i=0;$i -le $dataGroup1.data.Count-1; $i++) { 
    $prodDate = (Get-Date).AddDays(-3).ToString("yyyy-MM-dd")

    $header = @{Authorization='Bearer '+$token.access_token}

    $uri = "<url path>" + $dataGroup1.data[$i].id + "/readings?" + $prodDate

    $readings = Invoke-RestMethod -Header $header -Uri $uri -UseBasicParsing

    For ($f=0;$f -le $readings.data.Count-1; $f++)
    {
        If ($($readings.data[$f].id) -gt 0)
        {
            "INSERT INTO dbo.Table1       
            VALUES('$($readings.data[$f].id)'
            ,'$($dataGroup1.data[$i].id)'
            ,convert(date,dateadd(S,$($readings.data[$f].reading_time),'1970-01-01'))
            ,'$($readings.data[$f].Data1)'
            ,'$($readings.data[$f].Data2)'
            ,'$($readings.data[$f].Data3)'
            ,'$($readings.data[$f].Data4)'
            ,'$($readings.data[$f].Data5)'
            ,'$($readings.data[$f].Data6)'
            ,'$($readings.data[$f].Data7)'
            ,'$($readings.data[$f].Data8)'
            ,convert(date,dateadd(S,$($readings.data[$f].Data9), '1970-01-01'))
            ,'$($readings.data[$f].Data10)'
            ,GETDATE())"
        } 
        Start-Sleep -Seconds 3
    }
}

核心优化措施 #

1. 并行处理API请求 #

把串行的嵌套循环改成并行调用，直接减少API请求的总耗时：

• PowerShell 7及以上版本用ForEach-Object -Parallel，设置合理的-ThrottleLimit（比如5-10）控制并发数，避免触发API限流
• 旧版本PowerShell可以用Start-Job，但注意及时清理作业避免资源泄漏

2. 动态应对API限流，删除固定Sleep #

• 不要每次请求都固定等3秒，改成检查API返回的限流头：比如RateLimit-Remaining（剩余请求数）、RateLimit-Reset（重置时间），当剩余请求不足时再等待
• 如果收到429（请求过多）响应，读取Retry-After头的值作为等待时间，精准适配API限流规则

3. 批量插入SQL，告别单条INSERT #

• 用SqlBulkCopy批量写入数据，这是SQL Server最快的批量插入方式，比单条INSERT效率提升几十倍
• 如果必须用T-SQL语句，生成多行INSERT（INSERT INTO Table VALUES (...), (...)），减少和数据库的交互次数

4. 清理脚本冗余代码 #

• 授权头$header只需要初始化一次，不用在循环里重复创建
• $prodDate提前计算好，避免每次循环都重新生成日期字符串
• 用foreach遍历集合替代索引式for循环，代码更简洁，性能也更好

优化后的示例脚本（PowerShell 7+） #

# 初始化授权信息
$token = Invoke-RestMethod -Uri https://<uri path> -Body $body -Method Post -UseBasicParsing
$header = @{Authorization = "Bearer $($token.access_token)"}
$prodDate = (Get-Date).AddDays(-3).ToString("yyyy-MM-dd")

# 获取数据组列表
$dataGroup1 = Invoke-RestMethod -Header $header -Uri https://<pathname> -UseBasicParsing

# 并行获取所有读数数据，带限流重试逻辑
$allReadings = $dataGroup1.data | ForEach-Object -Parallel {
    $item = $_
    $header = $using:header
    $prodDate = $using:prodDate
    $uri = "<url path>$($item.id)/readings?$prodDate"

    $retryCount = 0
    $maxRetries = 3
    do {
        try {
            $response = Invoke-RestMethod -Header $header -Uri $uri -UseBasicParsing -ErrorAction Stop
            
            # 动态处理限流
            $remaining = [int]$response.Headers['RateLimit-Remaining']
            if ($remaining -lt 5) {
                $reset = [int]$response.Headers['RateLimit-Reset']
                $waitTime = $reset - (Get-Date).ToUnixTimeSeconds()
                if ($waitTime -gt 0) { Start-Sleep -Seconds $waitTime }
            }

            # 过滤有效数据并整理成插入格式
            $response.data | Where-Object { $_.id -gt 0 } | Select-Object `
                @{Name='ReadingId'; Expression={$_.id}},
                @{Name='GroupId'; Expression={$item.id}},
                @{Name='ReadingTime'; Expression={[DateTimeOffset]::FromUnixTimeSeconds($_.reading_time).DateTime}},
                Data1, Data2, Data3, Data4, Data5, Data6, Data7, Data8,
                @{Name='Data9Date'; Expression={[DateTimeOffset]::FromUnixTimeSeconds($_.Data9).DateTime}},
                Data10,
                @{Name='InsertTime'; Expression={Get-Date}}
            break
        }
        catch {
            $retryCount++
            if ($_.Exception.Response.StatusCode -eq 429) {
                $retryAfter = [int]$_.Exception.Response.Headers['Retry-After']
                Start-Sleep -Seconds $retryAfter
            }
            elseif ($retryCount -ge $maxRetries) {
                Write-Warning "获取ID $($item.id) 数据失败，已重试$maxRetries次"
                break
            }
            else {
                Start-Sleep -Seconds (2 * $retryCount) # 指数退避重试
            }
        }
    } while ($retryCount -lt $maxRetries)
} -ThrottleLimit 8 # 根据API限流规则调整并发数

# 批量插入到SQL Server
if ($allReadings) {
    $connectionString = "Server=你的服务器;Database=你的数据库;Integrated Security=True;"
    $sqlConn = New-Object System.Data.SqlClient.SqlConnection($connectionString)
    $sqlConn.Open()

    $bulkCopy = New-Object System.Data.SqlClient.SqlBulkCopy($sqlConn)
    $bulkCopy.DestinationTableName = "dbo.Table1"

    # 列映射（如果对象属性名和表列名一致可省略，否则需手动对应）
    $bulkCopy.ColumnMappings.Add("ReadingId", "id")
    $bulkCopy.ColumnMappings.Add("GroupId", "dataGroupId") # 替换为表中实际列名
    $bulkCopy.ColumnMappings.Add("ReadingTime", "reading_time")
    $bulkCopy.ColumnMappings.Add("Data1", "Data1")
    $bulkCopy.ColumnMappings.Add("Data2", "Data2")
    $bulkCopy.ColumnMappings.Add("Data3", "Data3")
    $bulkCopy.ColumnMappings.Add("Data4", "Data4")
    $bulkCopy.ColumnMappings.Add("Data5", "Data5")
    $bulkCopy.ColumnMappings.Add("Data6", "Data6")
    $bulkCopy.ColumnMappings.Add("Data7", "Data7")
    $bulkCopy.ColumnMappings.Add("Data8", "Data8")
    $bulkCopy.ColumnMappings.Add("Data9Date", "Data9")
    $bulkCopy.ColumnMappings.Add("Data10", "Data10")
    $bulkCopy.ColumnMappings.Add("InsertTime", "InsertTime")

    # 转换为DataTable
    $dataTable = New-Object System.Data.DataTable
    $allReadings[0].PSObject.Properties | ForEach-Object {
        $dataTable.Columns.Add($_.Name, $_.Value.GetType()) | Out-Null
    }
    $allReadings | ForEach-Object {
        $row = $dataTable.NewRow()
        $_.PSObject.Properties | ForEach-Object {
            $row[$_.Name] = $_.Value
        }
        $dataTable.Rows.Add($row)
    }

    $bulkCopy.WriteToServer($dataTable)
    $sqlConn.Close()
}

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