如何通过单次Invoke-Sqlcmd批量执行SQL提升PowerShell脚本效率？

针对你遇到的多次调用Invoke-Sqlcmd导致脚本运行缓慢的问题，核心优化思路是减少数据库连接次数和合并查询操作，以下是具体解决方案：

1. 在单个Invoke-Sqlcmd中执行多个SQL语句 #

SQL支持用分号;分隔多个查询语句，你可以把所有需要执行的SQL合并到一个Query参数中，这样Invoke-Sqlcmd只会建立一次数据库连接，批量执行所有查询。

使用-OutputAs DataSet参数可以获取每个查询对应的结果集，之后通过Tables索引提取对应数据：

# 合并多个SQL语句，分号分隔
$combinedQuery = @"
SELECT * FROM something WHERE ID = 150;
SELECT * FROM something WHERE ID = 100;
"@

# 执行批量查询，返回包含多个结果集的DataSet
$results = Invoke-Sqlcmd -ServerInstance "Something" -Database "Something" -Query $combinedQuery -OutputAs DataSet

# 提取第一个查询的结果（对应ID=150）
$ProjectName = $results.Tables[0]

# 提取第二个查询的结果（对应ID=100）
$Comments = $results.Tables[1]

2. 合并相同表的查询为单次IN查询 #

如果你的50次查询都是针对同一张表、仅ID不同，直接用IN子句合并成一个查询，一次性获取所有需要的数据，这比多次查询效率高得多：

# 整理所有需要查询的ID
$targetIds = @(150, 100, 123, 456) # 替换成你的50个ID

# 将ID列表转为逗号分隔的字符串
$idList = $targetIds -join ','

# 单次查询获取所有目标数据
$allMetadata = Invoke-Sqlcmd -ServerInstance "Something" -Database "Something" -Query "SELECT * FROM something WHERE ID IN ($idList)"

# 按需筛选对应ID的数据
$ProjectName = $allMetadata | Where-Object { $_.ID -eq 150 }
$Comments = $allMetadata | Where-Object { $_.ID -eq 100 }

3. 手动管理数据库连接（更灵活的场景） #

如果需要更精细地控制连接生命周期，可以直接使用.NET的SqlConnection对象手动管理连接，避免Invoke-Sqlcmd每次自动创建/销毁连接的开销：

# 加载SQL客户端程序集
Add-Type -AssemblyName System.Data.SqlClient

# 构建连接字符串（根据你的认证方式调整，比如用SQL账号的话要加User ID和Password）
$connectionString = "Server=Something;Database=Something;Integrated Security=True;"

# 创建连接对象
$connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)

try {
    # 打开连接
    $connection.Open()

    # 执行第一个查询
    $cmdProject = New-Object System.Data.SqlClient.SqlCommand("SELECT * FROM something WHERE ID = 150", $connection)
    $readerProject = $cmdProject.ExecuteReader()
    $ProjectName = New-Object System.Data.DataTable
    $ProjectName.Load($readerProject)
    $readerProject.Close()

    # 执行第二个查询（复用同一个连接）
    $cmdComments = New-Object System.Data.SqlClient.SqlCommand("SELECT * FROM something WHERE ID = 100", $connection)
    $readerComments = $cmdComments.ExecuteReader()
    $Comments = New-Object System.Data.DataTable
    $Comments.Load($readerComments)
    $readerComments.Close()
}
finally {
    # 确保连接被关闭和释放
    $connection.Close()
    $connection.Dispose()
}

额外优化建议 #

• 避免SELECT *：只查询你需要的列，减少数据传输量和内存占用，比如SELECT ID, ProjectName FROM something
• 分页处理：针对100万文件的场景，不要一次性加载所有数据，用OFFSET ... FETCH NEXT做分页查询，分批处理
• 批量上传SharePoint：上传环节也可以优化，比如用Add-PnPFile的批量操作，或者SharePoint REST API的批量请求，避免逐个上传的开销

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